const oracledb = require('oracledb')

const xlsx = require('node-xlsx');
var fs = require('fs');

var moment = require('moment');

const DB_CONFIG = {
  user: 'cgxzcuser', 
  password: 'Tp3D1rBz#cx2024',
  //connectString: 'jdbc:oracle:thin:@172.50.100.181:1521:cgxzc' // 数据库地址：{IP:PORT/数据库名称}
  connectString: '172.50.100.181:1521/cgxzc' // 数据库地址：{IP:PORT/数据库名称}
}


console.log('运行模式:', oracledb.thin ? 'Thin' : 'Thick');

//oracledb.initOracleClient();
oracledb.initOracleClient({libDir: "C:\\Users\\15371\\scoop\\apps\\oracle-instant-client\\current"});

// oracledb.getConnection(DB_CONFIG).then( async connection => {
//   console.log('Connected successfully!');
//   // 执行 SQL 查询
//    result = await connection.execute(`SELECT * FROM AA_AARF`);
//       console.log('result:',result)
// }).catch(err => {
//   console.error('Connection error:', err);
// });

const 新产销与老产销=`
select
    S1.ORDER_NO
     ,S1.ORDER_NO_OLD
     ,S1.order_item_num
     ,CASE WHEN NVL(S1.CONTRACT_NO_CUST,'N/A')=客户合同号 THEN '相同' else S1.CONTRACT_NO_CUST ||' ；老：'||客户合同号 end  as  客户合同号
     ,S1.TRADE_TYPE
     ,S1.ORDER_CLASSIFY
     ,S1.ORDER_MODE
     ,S1.ORDER_TYPE ||' ；老：'||合同类型描述  as  合同类型描述
     ,CASE WHEN to_char(S1.SALE_INS)=销售组织 THEN '相同' else to_char(S1.SALE_INS) ||' ；老：'||销售组织 end  as  销售组织
     ,CASE WHEN to_char(S1.SALE_ORG)=销售区域 THEN '相同' else to_char(S1.SALE_ORG) ||' ；老：'||销售区域 end  as  销售区域
     ,CASE WHEN to_char(S1.SALESMAN)=创建人 THEN '相同' else to_char(S1.SALESMAN) ||' ；老：'||创建人 end  as  创建人
     ,CASE WHEN to_char(S1.DOCUMENTARY_NAME)=跟单人 THEN '相同'
           WHEN NVL(S1.DOCUMENTARY_NAME, 'H')=NVL(跟单人, 'H') THEN '相同'
           else to_char(S1.DOCUMENTARY_NAME) ||' ；老：'||跟单人 end  as  跟单人
     ,S1.SIGN_DATE
     ,S1.BUSINESS_TYPE
     ,S1.SIGN_ADDRESS
     ,S1.COMPANY_ID ||' ；老：'||首次受票人  as  首次受票人
     ,CASE WHEN to_char(S1.CUSTOMER_NM)=客户名称 THEN '相同'
           when md1.code = S1.CUSTOMER_NO then '相同'
           WHEN s2.CUSTOMER_NO = '8000000617' THEN '相同'
           WHEN s2.CUSTOMER_NO = '1440002305' THEN '相同'
           else to_char(S1.CUSTOMER_NM) ||' ；老：'||客户名称 end  as  客户名称
     ,CASE WHEN to_char(S1.STLMT_UNIT_NM)=结算单位名称 THEN '相同'
           when md2.code = S1.STLMT_UNIT_NO then '相同'
           WHEN S2.STLMT_UNIT_NO = '8000000617' THEN '相同'
           WHEN S2.STLMT_UNIT_NO = '1440002305' THEN '相同'
           else to_char(S1.STLMT_UNIT_NM) ||' ；老：'||结算单位名称 end  as  结算单位名称
     ,S1.PRODUCE_PLACE
     ,S1.SUPPLY_UNIT ||' ；老：'||制造商  as  制造商
     ,S1.FREIGHT_MODE
     ,S1.INVOICE_TYPE
     ,S1.BILL_DATE
     ,S1.BANK
     ,S1.ACCOUNT
     ,S1.TAX_ID
     ,CASE WHEN NVL(S1.PRICE_FL,'N/A')=计价方式 THEN '相同' else to_char(S1.PRICE_FL) ||' ；老：'||计价方式 end  as  计价方式
     ,CASE WHEN to_char(S1.CURRENCY)=币种 THEN '相同' else to_char(S1.CURRENCY) ||' ；老：'||币种 end  as  币种
     ,CASE WHEN NVL(S1.MNY_ASK_TYP,'N/A')=付款方式 THEN '相同' else to_char(S1.MNY_ASK_TYP) ||' ；老：'||付款方式 end  as  付款方式
     ,S1.PRICE_PLAN
     ,S1.LOCAL_LAW
     ,S1.ORDER_REQUIRE_STATUS
     ,S1.PORT_OF_DESTINATION
     ,S1.ORDER_SEQ
     ,S1.ORDER_SEQ_OLD
     ,S1.DELIVERY_NO_OLD
     ,S1.ORDER_STATUS
     ,S1.MONTH
     ,S1.GROUP_ORDER_NO
     ,S1.PROD_SPEC_CODE
     ,S1.PROD_SPEC
     ,S1.APN_CODE
     ,S1.APN_DEF
     ,CASE WHEN to_char(S1.PROD_NAME)=品名描述 THEN '相同' else to_char(S1.PROD_NAME) ||' ；老：'||品名描述 end  as  品名描述
     ,CASE WHEN to_char(S1.QLY_GRADE_NO)=NVL(标准类别描述,'无') THEN '相同' else to_char(S1.QLY_GRADE_NO) ||' ；老：'||标准类别描述 end  as  标准类别描述
     ,CASE WHEN to_char(S1.TRADE_NO)=钢级名称 THEN '相同' else to_char(S1.TRADE_NO) ||' ；老：'||钢级名称 end  as  钢级名称
     ,CASE WHEN to_char(S1.PROD_STD)=标准名称 THEN '相同' else to_char(S1.PROD_STD) ||' ；老：'||标准名称 end  as  标准名称
     ,S1.PIPE_TYPE
     ,S1.MANU_METHOD
     ,S1.DELIVERY_STATUS_NAME
     ,CASE WHEN to_char(S1.SALE_PROD_CODE_NAME)=品名描述 THEN '相同' else to_char(S1.SALE_PROD_CODE_NAME) ||' ；老：'||品名描述 end  as  订货品名描述
     ,CASE WHEN to_char(S1.SALE_SG_STD_NM)=标准名称 THEN '相同' else to_char(S1.SALE_SG_STD_NM) ||' ；老：'||标准名称 end  as  订货标准名称
     ,CASE WHEN to_char(S1.SALE_SHOP_SIGN_NAME)=钢级名称 THEN '相同' else to_char(S1.SALE_SHOP_SIGN_NAME) ||' ；老：'||钢级名称 end  as  订货钢级名称
     ,S1.SALE_GOOD_STATUS_NAME
     ,CASE WHEN S1.OUT_DIA=外径 THEN '相同'
           when S1.PROD_TYPE_NO = 'CQ' and  NVL(S1.OUT_DIA,0) != 0  then '相同'
           else S1.OUT_DIA ||' ；老：'||外径 end  as  外径
     ,CASE WHEN S1.WAL_THK=壁厚 THEN '相同'
           when S1.PROD_TYPE_NO = 'CQ' and  NVL(S1.WAL_THK,0) != 0  then '相同'
           else S1.WAL_THK ||' ；老：'||壁厚 end  as  壁厚
     ,CASE WHEN to_char(S1.MODEL_NAME)=扣型描述 THEN '相同' else to_char(S1.MODEL_NAME) ||' ；老：'||扣型描述 end  as  扣型描述
     ,CASE WHEN to_char(S1.SPEC_CODE_NAME)=产品规格描述 THEN '相同' else to_char(S1.SPEC_CODE_NAME) ||' ；老：'||产品规格描述 end  as  产品规格描述
     ,CASE WHEN to_char(S1.ORDER_SPEC_CODE_NAME)=订货规格描述 THEN '相同' else to_char(S1.ORDER_SPEC_CODE_NAME) ||' ；老：'||订货规格描述 end  as  订货规格描述
     ,CASE WHEN S1.WGT=交货量 THEN '相同'
           WHEN S1.id IN ('1927801479682134018', '1927844455376556034', '1927839200570474497')  then '相同'
           else S1.WGT ||' ；老：'||交货量 end  as  交货量
     ,CASE WHEN to_char(S1.UNIT)=订货计量单位 THEN '相同' else to_char(S1.UNIT) ||' ；老：'||订货计量单位 end  as  订货计量单位
     ,CASE WHEN S1.LEN_MIN=下限 THEN '相同' else S1.LEN_MIN ||' ；老：'||下限 end  as  长度下限
     ,CASE WHEN S1.LEN_MAX=上限 THEN '相同' else S1.LEN_MAX ||' ；老：'||上限 end  as  长度上限
     ,CASE WHEN to_char(S1.LEN_UNIT)=长度单位 THEN '相同' else to_char(S1.LEN_UNIT) ||' ；老：'||长度单位 end  as  长度单位
     ,CASE WHEN NVL(S1.ORD_ENSU_TOTL_LEN,0)=NVL(保总长,0) THEN '相同' else S1.ORD_ENSU_TOTL_LEN ||' ；老：'||保总长 end  as  保总长
     ,CASE WHEN nvl(S1.SHRT_LEN_RATO,0)=nvl(短尺率,0) THEN '相同' else S1.SHRT_LEN_RATO ||' ；老：'||短尺率 end  as  短尺率
     ,CASE WHEN S1.SHORTEST=nvl(短尺最短,0) THEN '相同' else S1.SHORTEST ||' ；老：'||短尺最短 end  as  短尺最短
     ,CASE WHEN to_char(S1.SHRT_STOVE)=短尺随炉 THEN '相同' else to_char(S1.SHRT_STOVE) ||' ；老：'||短尺随炉 end  as  短尺随炉
     ,CASE WHEN S1.AVG_LEN_MIN=nvl(平均长度大于,0) THEN '相同' else S1.AVG_LEN_MIN ||' ；老：'||平均长度大于 end  as  平均长度大于
     ,CASE WHEN S1.AVG_LEN_MAX=nvl(平均长度小于,0) THEN '相同' else S1.AVG_LEN_MAX ||' ；老：'||平均长度小于 end  as  平均长度小于
     ,CASE WHEN S1.DELVRY_RANGE_MIN=交货量差下限*-1 THEN '相同'
           when NVL(S1.DELVRY_RANGE_MIN, 0) < 0 AND NVL(交货量差下限,0) = ABS(NVL(S1.DELVRY_RANGE_MIN, 0))  then '相同'
           else S1.DELVRY_RANGE_MIN ||' ；老：'||交货量差下限*-1 end  as  交货量差下限
     ,CASE WHEN S1.DELVRY_RANGE_MAX=交货量差上限 THEN '相同' else S1.DELVRY_RANGE_MAX ||' ；老：'||交货量差上限 end  as  交货量差上限
     ,CASE WHEN nvl(S1.SIGL_LEN,0)=nvl(单倍尺长,0) THEN '相同' else S1.SIGL_LEN ||' ；老：'||单倍尺长 end  as  单倍尺长
--         ,s1.DELVRY_EDATE ||' ；老：'||TO_CHAR(交货结束日期, 'YYYY-MM-DD')  as  交货结束日期
     ,CASE WHEN TO_CHAR(s1.DELVRY_EDATE) =TO_CHAR(交货结束日期, 'YYYY-MM-DD') THEN '相同' else TO_CHAR(s1.DELVRY_EDATE) ||' ；老：'||TO_CHAR(交货结束日期, 'YYYY-MM-DD') end  as  交货结束日期
     ,CASE WHEN to_char(S1.CONSIGNEE_COM)=收货单位名称 THEN '相同' else to_char(S1.CONSIGNEE_COM) ||' ；老：'||收货单位名称 end  as  收货单位名称
     ,CASE WHEN to_char(S1.TRANS_TYPE)=出厂方式 THEN '相同' else to_char(S1.TRANS_TYPE) ||' ；老：'||出厂方式 end  as  出厂方式
     ,CASE WHEN to_char(S1.TRANS_MODE)=代办标识 THEN '相同' else to_char(S1.TRANS_MODE) ||' ；老：'||代办标识 end  as  代办标识
     ,S1.STATION_NAME
     ,S1.SPCL_LN_NM
     ,S1.MEMO
     ,CASE WHEN to_char(S1.FINAL_USER_NM)=最终用户 THEN '相同'
           when md3.code = S1.FINAL_USER then '相同'
           WHEN S2.FINAL_USER = '8000000617' THEN '相同'
           else to_char(S1.FINAL_USER_NM) ||' ；老：'||最终用户 end  as  最终用户
     ,CASE WHEN to_char(S1.QUALITY_USER)=质保书单位 THEN '相同' else to_char(S1.QUALITY_USER) ||' ；老：'||质保书单位 end  as  质保书单位
     ,CASE WHEN to_char(S1.OUTBOUND_CALCULATE)=出库计量 THEN '相同' else to_char(S1.OUTBOUND_CALCULATE) ||' ；老：'||出库计量 end  as  出库计量
     ,CASE WHEN to_char(S1.OUTS_BILLET_FL)=外购坯 THEN '相同' else to_char(S1.OUTS_BILLET_FL) ||' ；老：'||外购坯 end  as  外购坯
     ,CASE WHEN to_char(nvl(S1.OUTS_PIPE,'/'))=外购管 THEN '相同' else to_char(S1.OUTS_PIPE) ||' ；老：'||外购管 end  as  外购管
     ,CASE WHEN to_char(S1.MONITOR_FLAG)=监制要求 THEN '相同' else to_char(S1.MONITOR_FLAG) ||' ；老：'||监制要求 end  as  监制要求
     ,CASE WHEN to_char(S1.MAN_INSPECTION)=在线机探盲区 THEN '相同' else to_char(S1.MAN_INSPECTION) ||' ；老：'||在线机探盲区 end  as  在线机探盲区
     ,在线机探盲区切长
--        case when to_char(NVL(d.INSPECTION_VALUE,0)) = NVL(b.INSPECTION_VALUE,'0')  then '相同' else to_char(d.INSPECTION_VALUE) ||',新产销：'|| to_char(b.INSPECTION_VALUE) end 		"在线机探盲区切长",
     ,S1.PROJECT_NO
     ,CASE WHEN to_char(S1.COOPERATE_FLAG)=是否协同 THEN '相同' else to_char(S1.COOPERATE_FLAG) ||' ；老：'||是否协同 end  as  是否协同
     ,CASE WHEN to_char(S1.EXTRA_REQUIRE_OLD)=订货附加要求 THEN '相同' else '不同' end  as  订货附加要求
     ,CASE WHEN to_char(S1.PRO_TEC_REQUIRE_OLD)=特殊要求技术规范 THEN '相同' else '不同' end  as  特殊要求技术规范
     ,CASE WHEN to_char(S1.KEEP_TYPE)=保价类型 THEN '相同' else to_char(S1.KEEP_TYPE) ||' ；老：'||保价类型 end  as  保价类型
     ,CASE WHEN S1.FREIGHT=nvl(运费,0) THEN '相同' else S1.FREIGHT ||' ；老：'||运费 end  as  运费
     ,CASE WHEN S1.OUT_PRICE=nvl(出厂价,0) THEN '相同' else S1.OUT_PRICE ||' ；老：'||出厂价 end  as  出厂价
     ,CASE WHEN S1.PAK_PRICE=nvl(吊装费,0) THEN '相同' else S1.PAK_PRICE ||' ；老：'||吊装费 end  as  吊装费
     ,CASE WHEN S1.BALNCE_PRICE=nvl(结算价,0) THEN '相同' else S1.BALNCE_PRICE ||' ；老：'||结算价 end  as  结算价
     ,s2.付款要求描述
     ,s2.合同状态
     ,s2.生效人
     ,s2.修改人
     ,s2.是否含税
     ,s2.合同分类
     ,s2.打印价格
     ,s2.优惠方案
     ,s2.合同归档
     ,s2.品种分类
     ,s2.产品规范描述
     ,s2.冶金规范描述
     ,s2.用途码描述
     ,s2.长度描述
     ,s2.长尺率
     ,s2.长尺最长
     ,s2.成品公差
     ,s2.交货量差方式
     ,s2.ASSEL
     ,s2.监制说明
     ,s2.MPS要求
     ,s2.MPS编号
     ,s2.MPS说明
     ,s2.订货价
     ,s2.测算价
     ,s2.吨价
     ,s2.合同行状态
     ,s2.结算价状态
     ,s2.WBS号
     ,s2.运输方式
     ,s2.管端自动测量
     ,s2.攻关项目
     ,s2.输出标准号
     ,s2.重量估算
     ,s2.交货地点
     ,s2.指定地址
     ,s2.运输方案
     ,s2.自提标志
from (
         SELECT
             t2.id,
             t1.order_no,--合同号
             t1.ORDER_NO_OLD,--老产销订单
             t2.ORDER_NO_OLD || '/' ||t2.ORDER_SEQ_OLD || '/' || t2.DELIVERY_NO_OLD AS order_item_num ,
             t1.CONTRACT_NO_CUST,--客户合同号
             t1.TRADE_TYPE,--贸易类型
             t1.CUSTOMER_NO,
             t1.STLMT_UNIT_NO,
             t2.PROD_TYPE_NO,
             case when (select  STR_VALUE from SYS_DICTIONARY_DTL@CGXZCDB_ZSJ  t where t.DICT_SN='ORDER_CLASSIFY' and t.STR_KEY=t1.ORDER_CLASSIFY) is not null then (select  STR_VALUE from SYS_DICTIONARY_DTL@CGXZCDB_ZSJ  t where t.DICT_SN='ORDER_CLASSIFY' and t.STR_KEY=t1.ORDER_CLASSIFY) else  t1.ORDER_CLASSIFY end ORDER_CLASSIFY,--分类
             (select  STR_VALUE from SYS_DICTIONARY_DTL@CGXZCDB_ZSJ  t where t.DICT_SN='ORDER_MODE' and t.STR_KEY=t1.ORDER_MODE  ) ORDER_MODE,--合同类型
             (select  STR_VALUE from SYS_DICTIONARY_DTL@CGXZCDB_ZSJ  t where t.DICT_SN='ORDER_TYPE' and t.STR_KEY=t1.ORDER_TYPE ) ORDER_TYPE,--分类--订货方式
             (select  STR_VALUE from SYS_DICTIONARY_DTL@CGXZCDB_ZSJ  t where t.DICT_SN='saleIns' and t.STR_KEY=t1.SALE_INS ) SALE_INS,--销售组织
             (select  STR_NAME from hr_department@CGXZCDB_ZSJ t where STR_SN=SALE_ORG) SALE_ORG,--片区
-- 		ORDER_YEAR,--合同年度
             case when  (select  NAME from HR_USER@CGXZCDB_ZSJ where USER_NO=t1.SALESMAN) is not null then (select  NAME from HR_USER@CGXZCDB_ZSJ where USER_NO=t1.SALESMAN) else  t1.SALESMAN end SALESMAN,--业务员
             case when  (select  NAME from HR_USER@CGXZCDB_ZSJ where USER_NO=t1.DOCUMENTARY_NAME) is not null then (select  NAME from HR_USER@CGXZCDB_ZSJ where USER_NO=t1.DOCUMENTARY_NAME) else  t1.DOCUMENTARY_NAME end DOCUMENTARY_NAME,--跟单人
             t1.SIGN_DATE,--签订日期
             case when t1.BUSINESS_TYPE='1' then '出口' else '非出口' end BUSINESS_TYPE,--业务分类
             t1.SIGN_ADDRESS,--签订地点
             (select  STR_VALUE from SYS_DICTIONARY_DTL@CGXZCDB_ZSJ  t where t.DICT_SN='saleOrg' and t.STR_KEY=t1.COMPANY_ID) COMPANY_ID,--卖出单位
             t1.CUSTOMER_NM,--订货单位
             t1.STLMT_UNIT_NM,--结算单位
             case when t1.PRODUCE_PLACE='Z' then '自产' else '外购' end PRODUCE_PLACE,--产地区分
             (select  STR_VALUE from SYS_DICTIONARY_DTL@CGXZCDB_ZSJ  t where t.DICT_SN='SUPPLY_UNIT' and t.STR_KEY=t1.SUPPLY_UNIT ) SUPPLY_UNIT,--制造商
             (select  STR_VALUE from SYS_DICTIONARY_DTL@CGXZCDB_ZSJ  t where t.DICT_SN='SETTLE_FREIGHT_MODE' and t.STR_KEY=t1.FREIGHT_MODE )  FREIGHT_MODE,--运费开票方式
             (select  STR_VALUE from SYS_DICTIONARY_DTL@CGXZCDB_ZSJ  t where t.DICT_SN='ORDER_INVOICE_TYPE' and t.STR_KEY=t1.INVOICE_TYPE ) INVOICE_TYPE,--发票类型
             (select  STR_VALUE from SYS_DICTIONARY_DTL@CGXZCDB_ZSJ  t where t.DICT_SN='saleBillDate' and t.STR_KEY=t1.BILL_DATE ) BILL_DATE,
             t1.BANK,--开户行
             t1.ACCOUNT,--开户行账号
             t1.TAX_ID,--税号
             (select  STR_VALUE from SYS_DICTIONARY_DTL@CGXZCDB_ZSJ  t where t.DICT_SN='PRICE_FL' and t.STR_KEY=t1.PRICE_FL) PRICE_FL,--定价方式
-- 		CONTAIN_TAX,--是否含税
             (select  STR_VALUE from SYS_DICTIONARY_DTL@CGXZCDB_ZSJ  t where t.DICT_SN='currency' and t.STR_KEY=t1.CURRENCY)  CURRENCY,--币种
             (select  STR_VALUE from SYS_DICTIONARY_DTL@CGXZCDB_ZSJ  t where t.DICT_SN='MNY_ASK_TYP' and t.STR_KEY=t1.MNY_ASK_TYP)  MNY_ASK_TYP,--付款方式
             t1.PRICE_PLAN,--优惠方案
-- 		MNY_ASK_DESC,--付款要求
             t1.LOCAL_LAW,--当地法律法规
             '',--其他信息
             t1.ORDER_REQUIRE_STATUS,--订单要求
             t1.PORT_OF_DESTINATION,--目的港
-- 		'',--罚则
-- 		PENALTY_DESC--罚则条款
             t2.ORDER_SEQ,--合同行
             t2.ORDER_SEQ_OLD,--老产销合同行
             t2.DELIVERY_NO_OLD,--老产销交货行
             (select  STR_VALUE from SYS_DICTIONARY_DTL@CGXZCDB_ZSJ  t where t.DICT_SN='orderDtlStatus' and t.STR_KEY=t2.ORDER_STATUS )  ORDER_STATUS,--状态
             t2.MONTH,--排产月份
             t2.GROUP_ORDER_NO||'/'||GROUP_ORDER_SEQ GROUP_ORDER_NO, --集团订单号
             t2.PROD_SPEC_CODE,--规范码
             t2.PROD_SPEC,--产品规范描述
             t2.APN_CODE,--用途码代码
             t2.APN_DEF,--用途码描述
             t2.PROD_NAME,--规范品名
             (select  STR_VALUE from SYS_DICTIONARY_DTL@CGXZCDB_ZSJ  t where t.DICT_SN='qualityLevels' and t.STR_VALUE2=t2.QLY_GRADE_NO  ) QLY_GRADE_NO,--质量等级
             t2.TRADE_NO,--规范钢级
             t2.PROD_STD,--规范标准
             (select  STR_VALUE from SYS_DICTIONARY_DTL@CGXZCDB_ZSJ  t where t.DICT_SN='pipeType' and t.STR_KEY=t2.PIPE_TYPE  )  PIPE_TYPE,--管端类型
             (select  STR_VALUE from SYS_DICTIONARY_DTL@CGXZCDB_ZSJ  t where t.DICT_SN='manuMethod' and t.STR_KEY=t2.MANU_METHOD  ) MANU_METHOD,--制造方式
             t2.DELIVERY_STATUS_NAME,--规范交货状态
             t2.SALE_PROD_CODE_NAME,--订货品名
             t2.SALE_SG_STD_NM,--订货标准
             t2.SALE_SHOP_SIGN_NAME,--订货钢级
             t2.SALE_GOOD_STATUS_NAME,--订货交货状态
             t2.OUT_DIA,--外径
             t2.WAL_THK,--壁厚
             t2.MODEL_NAME,--扣型
             t2.SPEC_CODE_NAME,--产品规格
             t2.ORDER_SPEC_CODE_NAME,--订货规格
             t2.WGT,--订货量
             (select  STR_VALUE from SYS_DICTIONARY_DTL@CGXZCDB_ZSJ  t where t.DICT_SN='orderUnit' and t.STR_KEY=t2.UNIT  ) UNIT,--订货单位
             t2.LEN_MIN,--长度下限
             t2.LEN_MAX,--长度上限
             (select  STR_VALUE from SYS_DICTIONARY_DTL@CGXZCDB_ZSJ  t where t.DICT_SN='LEN_UNIT' and t.STR_KEY=t2.LEN_UNIT  ) LEN_UNIT,--长度单位
             t2.ORD_ENSU_TOTL_LEN,--保总长
             t2.SHRT_LEN_RATO,--短尺率
             t2.SHORTEST,--短尺最短
             t2.SHRT_STOVE,--短尺随炉
             t2.AVG_LEN_MIN,--平均长度大于
             t2.AVG_LEN_MAX,--平均长度小于
             t2.DELVRY_RANGE_MIN,--交货量差下限
             t2.DELVRY_RANGE_MAX,--交货量差上限
             t2.SIGL_LEN,--单倍长度
             t2.DELVRY_EDATE,--交货期
             t2.CONSIGNEE_COM,--收货单位
             (select  STR_VALUE from SYS_DICTIONARY_DTL@CGXZCDB_ZSJ  t where t.DICT_SN='TRANS_TYPE' and t.STR_KEY=t2.TRANS_TYPE  ) TRANS_TYPE,--运输方式
             (select  STR_VALUE from SYS_DICTIONARY_DTL@CGXZCDB_ZSJ  t where t.DICT_SN='transMode' and t.STR_KEY=t2.TRANS_MODE  ) TRANS_MODE,--运输模式
             t2.STATION_NAME,--到站
             t2.SPCL_LN_NM,--专用线
             t2.MEMO,--备注
             t2.FINAL_USER_NM,--最终用户
             t2.FINAL_USER,
             (select  STR_VALUE from SYS_DICTIONARY_DTL@CGXZCDB_ZSJ  t where t.DICT_SN='so_order_qualityUser' and t.STR_KEY=t2.QUALITY_USER  ) QUALITY_USER,--质保书单位
             (select  STR_VALUE from SYS_DICTIONARY_DTL@CGXZCDB_ZSJ  t where t.DICT_SN='OUTBOUND_CALCULATE' and t.STR_KEY=t2.OUTBOUND_CALCULATE  ) OUTBOUND_CALCULATE,--出库计量
             case when OUTS_BILLET_FL='1' then '外购' else '自炼' end OUTS_BILLET_FL,--外购坯
             (select  STR_VALUE from SYS_DICTIONARY_DTL@CGXZCDB_ZSJ  t where t.DICT_SN='order_dtl_outs_type' and t.STR_KEY=t2.OUTS_PIPE  ) OUTS_PIPE,--外购管
             case when MONITOR_FLAG='1' then '监制' else '不监制' end MONITOR_FLAG,--是否监制
             (select  STR_VALUE from SYS_DICTIONARY_DTL@CGXZCDB_ZSJ  t where t.DICT_SN='MAN_INSPECTION' and t.STR_KEY=t2.MAN_INSPECTION  ) MAN_INSPECTION,--在线机探盲区
             t2.PROJECT_NO,--订单工程编号
             case when COOPERATE_FLAG='1' then '是' else '否' end COOPERATE_FLAG,--是否协同
             t2.EXTRA_REQUIRE_OLD,--合同附加要求
             t2.PRO_TEC_REQUIRE_OLD,--产品技术要求
             (select  STR_VALUE from SYS_DICTIONARY_DTL@CGXZCDB_ZSJ  t where t.DICT_SN='PROTECT_TYPE' and t.STR_KEY=t2.KEEP_TYPE  ) KEEP_TYPE,--保价类型
             t2.FREIGHT,--运费
             t2.OUT_PRICE,--出厂价
             t2.PAK_PRICE,--吊装费
             t2.BALNCE_PRICE--结算价
         from SO_ORDER@CGXZCDB_ZSJ t1  left join  SO_ORDER_dtl@CGXZCDB_ZSJ   t2 on t1.order_no=t2.order_no  where  t1.order_status<>'XX' and t2.order_status<>'XX'  and t1.order_type<>'X'
--                                                                                                          and  t1.ORDER_NO_old = 'XSXPQ-25A1468'
     ) s1 left JOIN
     (SELECT
          t.ORDER_NO AS 合同号,
          T1.ORDER_SEQ AS 合同行号,
          T2.DELIVERY_NO AS 交货行号,
          t.CUSTM_ORDER_NO AS 客户合同号,
          t.ORDER_TYP_DESC AS 合同类型描述,
          t.CUSTOMER_NM AS 客户名称,
          t.CUSTOMER_NO,
          ( SELECT BASENAME FROM COM_BASE_INFO@TGCXDB_105 WHERE BASECODE = t.CURRENCY ) AS 币种,
          ( SELECT BASENAME FROM COM_BASE_INFO@TGCXDB_105 WHERE BASECODE = t.PRICE_FL ) AS 计价方式,
          ( SELECT BASENAME FROM COM_BASE_INFO@TGCXDB_105 WHERE BASECODE = t.MNY_ASK_TYP ) AS 付款方式,
          ( SELECT BASENAME FROM COM_BASE_INFO@TGCXDB_105 WHERE BASECODE = t.SUPPLY_UNIT ) AS 制造商,
          t.STLMT_UNIT_NM AS 结算单位名称,
          t.STLMT_UNIT_NO,
          ( SELECT BASENAME FROM COM_BASE_INFO@TGCXDB_105 WHERE BASECODE = T.FST_DRAWEE_UNIT ) AS 首次受票人,
          t.MNY_ASK_DESC AS 付款要求描述,
          t.SALE_ORG_DESC AS 销售组织,
          t.SALE_AREA_DESC AS 销售区域,
          t.ORDER_STS_DESC AS 合同状态,
          t.EFFECT_MAN AS 生效人,
          t.CREATE_NAME AS 创建人,
          t.UPDATE_NAME AS 修改人,
          t.DOCUMENTARY_NAME AS 跟单人,
          DECODE( t.CONTAIN_TAX, '1', '是', '否' ) AS 是否含税 ,
          ( SELECT BASENAME FROM COM_BASE_INFO@TGCXDB_105 WHERE BASECODE = T.ORDER_CLASSIFY ) AS 合同分类 ,
          t.PRINT_PRICE AS 打印价格,
          t.PRICE_PLAN AS 优惠方案,
          DECODE( t.ORDER_ARCHIVE, '1', '是', '否' ) AS 合同归档,
          ( SELECT BASENAME FROM COM_BASE_INFO@TGCXDB_105 WHERE BASECODE = T1.PRD_CLS ) AS 品种分类,
          T1.PSC_DESC AS 产品规范描述,
          T1.MSC_DESC AS 冶金规范描述,
          T1.USE_DESC AS 用途码描述,
          T1.PRODUCNAME AS 品名描述,
          T1.STD_NAME AS 标准名称,
          T1.STEELNAME AS 钢级名称,
          T1.STD_STYLE_DESC AS 标准类别描述,
          T1.ORDER_SPEC_DESC AS 订货规格描述,
          T1.SPEC_NAME AS 产品规格描述,
          T1.DIMATER AS 外径,
          T1.HEIGHT AS 壁厚,
          T1.MODEL_DESC AS 扣型描述,
          T1.LEN_DESC AS 长度描述,
          T1.LEN_UNIT AS 长度单位,
          T1.LEN_MIN AS 下限,
          T1.LEN_MAX AS 上限,
          T1.SHRT_LEN_RATO AS 短尺率,
          T1.SHORTEST AS 短尺最短,
          T1.LONG_LEN_RATO AS 长尺率,
          T1.LONGEST AS 长尺最长,
          T1.SHRT_STOVE AS 短尺随炉,
          T1.AVG_LEN_MIN AS 平均长度大于,
          T1.AVG_LEN_MAX AS 平均长度小于,
          T1.ENDLEN_SPECSTD AS 成品公差,
          T1.SIGL_LEN AS 单倍尺长,
          T1.ORDER_UNIT AS 订货计量单位,
          T1.ORDER_QTY AS 订货量,
          T1.ORD_ENSU_TOTL_LEN AS 订货保总长,
          T1.PRDCT_WGT AS 生产吨数,
          T1.DELVRY_RANGE_TPE AS 交货量差方式,
          T1.DELVRY_RANGE_MIN AS 交货量差下限,
          T1.DELVRY_RANGE_MAX AS 交货量差上限,
          T1.FINAL_USER_DESC AS 最终用户,
          T1.FINAL_USER,
          ( SELECT BASENAME FROM COM_BASE_INFO@TGCXDB_105 WHERE BASECODE = T1.OUTS_BILLET_FL ) AS 外购坯,
          ( SELECT BASENAME FROM COM_BASE_INFO@TGCXDB_105 WHERE BASECODE = T1.OUTS_STL_PIPE_FL ) AS 外购管,
          DECODE( T1.ASSEL_FL, '1', '允许', '不允许' ) AS ASSEL,
          DECODE( T1.PRODUCER_FL, '1', '监制', '不监制' ) AS 监制要求,
          T1.PRDCR_DESC AS 监制说明,
          DECODE( T1.MPS_FL, '1', '有', '无' ) AS MPS要求,
          T1.MPS_NO AS MPS编号,
          T1.MPS_DESC AS MPS说明,
          T1.ORDER_ADD_DESC AS 订货附加要求,
          T1.ORDER_SPECIAL_HEAD AS 特殊要求技术规范,
          T1.ORDER_PRICE AS 订货价,
          T1.TRANS_PRICE AS 运费,
          T1.TEST_PRICE AS 测算价,
          T1.OUT_PRICE AS 出厂价,
          T1.BALNCE_PRICE AS 结算价,
          T1.PAK_PRICE AS 吊装费,
          T1.TON_PRICE AS 吨价,
          DECODE( T1.KEEP_TYPE, '1', '保总价', '0', '保单价' ) AS 保价类型,
          ( SELECT BASENAME FROM COM_BASE_INFO@TGCXDB_105 WHERE BASECODE = T1.ORDER_LN_STATUS ) AS 合同行状态,
          DECODE( T1.BALNCE_PRICE_STS, '1', '有确认', '录入' ) AS 结算价状态,
          DECODE( T1.MAN_INSPECTION, '1', '可手探', '不可手探' ) AS 在线机探盲区,
          T1.INSPECTION_VALUE AS 在线机探盲区切长,
          T1.QUALITY_USER_DESC AS 质保书单位 ,
          T1.WBS_CODE AS WBS号,
          DECODE( T1.OUTBOUND_CALCULATE, '1', '实单过磅', '2', '实单按尺码单', '0', '实单不过磅' ) 出库计量,
          DECODE( T1.ORDER_TEAM, '1', '是', '0', '否' ) 是否协同,
          DECODE( T1.MODE_OF_TRANSPORT, '1', '散货', '2', '集装箱' ) 运输方式,
          DECODE( T1.AUTO_MEASURE, '1', '是', '0', '否' ) 管端自动测量 ,
          T1.KEY_PROJECT_NAME AS 攻关项目 ,
          ( SELECT BASENAME FROM COM_BASE_INFO@TGCXDB_105 WHERE BASECODE = T1.OUTPUT_STD_NO ) 输出标准号,
          t2.DELVRY_EDATE AS 交货结束日期,
          t2.DELVRY_QTY AS 交货量,
          t2.WGT_ESTMT AS 重量估算,
          t2.ENSU_TOTL_LEN AS 保总长,
          t2.RECEIV_NM AS 收货单位名称,
          t2.DELVRY_ADDR AS 交货地点,
          DECODE( t2.AGENCY_FL, '1', '待办', '0', '自提' ) AS 代办标识,
          t2.RECEIV_ADDR AS 指定地址,
          ( SELECT BASENAME FROM COM_BASE_INFO@TGCXDB_105 WHERE BASECODE = t2.TRANSIT_TYP ) AS 出厂方式,
          t2.TRANSIT_PLAN AS 运输方案,
          DECODE( t2.PICKUP_FLAG, '1', '自提', '0', '否' ) AS 自提标志
      FROM
          SLM_ORDER_HEAD@TGCXDB_105 t
              INNER JOIN SLM_ORDER_LINE@TGCXDB_105 T1 ON T.ORD_PK = T1.ORD_PK
              INNER JOIN SLM_ORDER_LINE_DELIVERY@TGCXDB_105 T2 ON T.ORD_PK = T2.ORD_PK
              AND T1.ORD_LN_PK = T2.ORD_LN_PK
      WHERE
          t.VALIDFLAG = '1'  and t1.VALIDFLAG = '1' and t2.VALIDFLAG='1'
--           and T.ORDER_NO = 'XSXPQ-25A1468'
     ) s2 on s1.order_no_old = s2.合同号 and s1.ORDER_SEQ_OLD = s2.合同行号 and s1.DELIVERY_NO_OLD=s2.交货行号

          LEFT JOIN (
    SELECT CUSTOMER_NO, code,
           ROW_NUMBER() OVER (PARTITION BY CUSTOMER_NO ORDER BY id DESC) AS rn
    FROM MD_SUPPLY_OLD_SYSTEM_LINK@TGCXDB_105
)md1 ON md1.CUSTOMER_NO = s2.CUSTOMER_NO AND md1.rn = 1
          LEFT JOIN (
    SELECT CUSTOMER_NO, code,
           ROW_NUMBER() OVER (PARTITION BY CUSTOMER_NO ORDER BY id DESC) AS rn
    FROM MD_SUPPLY_OLD_SYSTEM_LINK@TGCXDB_105
)md2 ON md2.CUSTOMER_NO = s2.STLMT_UNIT_NO AND md2.rn = 1
          LEFT JOIN (
    SELECT CUSTOMER_NO, code,
           ROW_NUMBER() OVER (PARTITION BY CUSTOMER_NO ORDER BY id DESC) AS rn
    FROM MD_SUPPLY_OLD_SYSTEM_LINK@TGCXDB_105
)md3 ON md3.CUSTOMER_NO = s2.FINAL_USER AND md3.rn = 1

where ROWNUM<10


`

const 新产销 = `
select t1.*,t2.* from SO_ORDER@CGXZCDB_ZSJ t1  left join  SO_ORDER_dtl@CGXZCDB_ZSJ   t2 on t1.order_no=t2.order_no
where ROWNUM<10
`

const 新产销对比列 = `

SELECT
             t2.id AS id,
             t1.order_no AS "合同号",
             t1.ORDER_NO_OLD AS "老产销订单",
             t2.ORDER_NO_OLD || '/' || t2.ORDER_SEQ_OLD || '/' || t2.DELIVERY_NO_OLD AS order_item_num,
             t1.CONTRACT_NO_CUST AS "客户合同号",
             t1.TRADE_TYPE AS "贸易类型",
             t1.CUSTOMER_NO AS CUSTOMER_NO,
             t1.STLMT_UNIT_NO AS STLMT_UNIT_NO,
             t2.PROD_TYPE_NO AS PROD_TYPE_NO,
             CASE 
                 WHEN (SELECT STR_VALUE FROM SYS_DICTIONARY_DTL@CGXZCDB_ZSJ t WHERE t.DICT_SN = 'ORDER_CLASSIFY' AND t.STR_KEY = t1.ORDER_CLASSIFY) IS NOT NULL 
                 THEN (SELECT STR_VALUE FROM SYS_DICTIONARY_DTL@CGXZCDB_ZSJ t WHERE t.DICT_SN = 'ORDER_CLASSIFY' AND t.STR_KEY = t1.ORDER_CLASSIFY) 
                 ELSE t1.ORDER_CLASSIFY 
             END AS "分类",
             (SELECT STR_VALUE FROM SYS_DICTIONARY_DTL@CGXZCDB_ZSJ t WHERE t.DICT_SN = 'ORDER_MODE' AND t.STR_KEY = t1.ORDER_MODE) AS "合同类型",
             (SELECT STR_VALUE FROM SYS_DICTIONARY_DTL@CGXZCDB_ZSJ t WHERE t.DICT_SN = 'ORDER_TYPE' AND t.STR_KEY = t1.ORDER_TYPE) AS "分类--订货方式",
             (SELECT STR_VALUE FROM SYS_DICTIONARY_DTL@CGXZCDB_ZSJ t WHERE t.DICT_SN = 'saleIns' AND t.STR_KEY = t1.SALE_INS) AS "销售组织",
             (SELECT STR_NAME FROM hr_department@CGXZCDB_ZSJ t WHERE STR_SN = SALE_ORG) AS "片区",
             CASE 
                 WHEN (SELECT NAME FROM HR_USER@CGXZCDB_ZSJ WHERE USER_NO = t1.SALESMAN) IS NOT NULL 
                 THEN (SELECT NAME FROM HR_USER@CGXZCDB_ZSJ WHERE USER_NO = t1.SALESMAN) 
                 ELSE t1.SALESMAN 
             END AS "业务员",
             CASE 
                 WHEN (SELECT NAME FROM HR_USER@CGXZCDB_ZSJ WHERE USER_NO = t1.DOCUMENTARY_NAME) IS NOT NULL 
                 THEN (SELECT NAME FROM HR_USER@CGXZCDB_ZSJ WHERE USER_NO = t1.DOCUMENTARY_NAME) 
                 ELSE t1.DOCUMENTARY_NAME 
             END AS "跟单人",
             t1.SIGN_DATE AS "签订日期",
             CASE WHEN t1.BUSINESS_TYPE = '1' THEN '出口' ELSE '非出口' END AS "业务分类",
             t1.SIGN_ADDRESS AS "签订地点",
             (SELECT STR_VALUE FROM SYS_DICTIONARY_DTL@CGXZCDB_ZSJ t WHERE t.DICT_SN = 'saleOrg' AND t.STR_KEY = t1.COMPANY_ID) AS "卖出单位",
             t1.CUSTOMER_NM AS "订货单位",
             t1.STLMT_UNIT_NM AS "结算单位",
             CASE WHEN t1.PRODUCE_PLACE = 'Z' THEN '自产' ELSE '外购' END AS "产地区分",
             (SELECT STR_VALUE FROM SYS_DICTIONARY_DTL@CGXZCDB_ZSJ t WHERE t.DICT_SN = 'SUPPLY_UNIT' AND t.STR_KEY = t1.SUPPLY_UNIT) AS "制造商",
             (SELECT STR_VALUE FROM SYS_DICTIONARY_DTL@CGXZCDB_ZSJ t WHERE t.DICT_SN = 'SETTLE_FREIGHT_MODE' AND t.STR_KEY = t1.FREIGHT_MODE) AS "运费开票方式",
             (SELECT STR_VALUE FROM SYS_DICTIONARY_DTL@CGXZCDB_ZSJ t WHERE t.DICT_SN = 'ORDER_INVOICE_TYPE' AND t.STR_KEY = t1.INVOICE_TYPE) AS "发票类型",
             (SELECT STR_VALUE FROM SYS_DICTIONARY_DTL@CGXZCDB_ZSJ t WHERE t.DICT_SN = 'saleBillDate' AND t.STR_KEY = t1.BILL_DATE) AS BILL_DATE,
             t1.BANK AS "开户行",
             t1.ACCOUNT AS "开户行账号",
             t1.TAX_ID AS "税号",
             (SELECT STR_VALUE FROM SYS_DICTIONARY_DTL@CGXZCDB_ZSJ t WHERE t.DICT_SN = 'PRICE_FL' AND t.STR_KEY = t1.PRICE_FL) AS "定价方式",
             (SELECT STR_VALUE FROM SYS_DICTIONARY_DTL@CGXZCDB_ZSJ t WHERE t.DICT_SN = 'currency' AND t.STR_KEY = t1.CURRENCY) AS "币种",
             (SELECT STR_VALUE FROM SYS_DICTIONARY_DTL@CGXZCDB_ZSJ t WHERE t.DICT_SN = 'MNY_ASK_TYP' AND t.STR_KEY = t1.MNY_ASK_TYP) AS "付款方式",
             t1.PRICE_PLAN AS "优惠方案",
             t1.LOCAL_LAW AS "当地法律法规",
             '' AS "其他信息",
             t1.ORDER_REQUIRE_STATUS AS "订单要求",
             t1.PORT_OF_DESTINATION AS "目的港",
             t2.ORDER_SEQ AS "合同行",
             t2.ORDER_SEQ_OLD AS "老产销合同行",
             t2.DELIVERY_NO_OLD AS "老产销交货行",
             (SELECT STR_VALUE FROM SYS_DICTIONARY_DTL@CGXZCDB_ZSJ t WHERE t.DICT_SN = 'orderDtlStatus' AND t.STR_KEY = t2.ORDER_STATUS) AS "状态",
             t2.MONTH AS "排产月份",
             t2.GROUP_ORDER_NO || '/' || GROUP_ORDER_SEQ AS "集团订单号",
             t2.PROD_SPEC_CODE AS "规范码",
             t2.PROD_SPEC AS "产品规范描述",
             t2.APN_CODE AS "用途码代码",
             t2.APN_DEF AS "用途码描述",
             t2.PROD_NAME AS "规范品名",
             (SELECT STR_VALUE FROM SYS_DICTIONARY_DTL@CGXZCDB_ZSJ t WHERE t.DICT_SN = 'qualityLevels' AND t.STR_VALUE2 = t2.QLY_GRADE_NO) AS "质量等级",
             t2.TRADE_NO AS "规范钢级",
             t2.PROD_STD AS "规范标准",
             (SELECT STR_VALUE FROM SYS_DICTIONARY_DTL@CGXZCDB_ZSJ t WHERE t.DICT_SN = 'pipeType' AND t.STR_KEY = t2.PIPE_TYPE) AS "管端类型",
             (SELECT STR_VALUE FROM SYS_DICTIONARY_DTL@CGXZCDB_ZSJ t WHERE t.DICT_SN = 'manuMethod' AND t.STR_KEY = t2.MANU_METHOD) AS "制造方式",
             t2.DELIVERY_STATUS_NAME AS "规范交货状态",
             t2.SALE_PROD_CODE_NAME AS "订货品名",
             t2.SALE_SG_STD_NM AS "订货标准",
             t2.SALE_SHOP_SIGN_NAME AS "订货钢级",
             t2.SALE_GOOD_STATUS_NAME AS "订货交货状态",
             t2.OUT_DIA AS "外径",
             t2.WAL_THK AS "壁厚",
             t2.MODEL_NAME AS "扣型",
             t2.SPEC_CODE_NAME AS "产品规格",
             t2.ORDER_SPEC_CODE_NAME AS "订货规格",
             t2.WGT AS "订货量",
             (SELECT STR_VALUE FROM SYS_DICTIONARY_DTL@CGXZCDB_ZSJ t WHERE t.DICT_SN = 'orderUnit' AND t.STR_KEY = t2.UNIT) AS "订货单位",
             t2.LEN_MIN AS "长度下限",
             t2.LEN_MAX AS "长度上限",
             (SELECT STR_VALUE FROM SYS_DICTIONARY_DTL@CGXZCDB_ZSJ t WHERE t.DICT_SN = 'LEN_UNIT' AND t.STR_KEY = t2.LEN_UNIT) AS "长度单位",
             t2.ORD_ENSU_TOTL_LEN AS "保总长",
             t2.SHRT_LEN_RATO AS "短尺率",
             t2.SHORTEST AS "短尺最短",
             t2.SHRT_STOVE AS "短尺随炉",
             t2.AVG_LEN_MIN AS "平均长度大于",
             t2.AVG_LEN_MAX AS "平均长度小于",
             t2.DELVRY_RANGE_MIN AS "交货量差下限",
             t2.DELVRY_RANGE_MAX AS "交货量差上限",
             t2.SIGL_LEN AS "单倍长度",
             t2.DELVRY_EDATE AS "交货期",
             t2.CONSIGNEE_COM AS "收货单位",
             (SELECT STR_VALUE FROM SYS_DICTIONARY_DTL@CGXZCDB_ZSJ t WHERE t.DICT_SN = 'TRANS_TYPE' AND t.STR_KEY = t2.TRANS_TYPE) AS "运输方式",
             (SELECT STR_VALUE FROM SYS_DICTIONARY_DTL@CGXZCDB_ZSJ t WHERE t.DICT_SN = 'transMode' AND t.STR_KEY = t2.TRANS_MODE) AS "运输模式",
             t2.STATION_NAME AS "到站",
             t2.SPCL_LN_NM AS "专用线",
             t2.MEMO AS "备注",
             t2.FINAL_USER_NM AS "最终用户",
             t2.FINAL_USER AS FINAL_USER,
             (SELECT STR_VALUE FROM SYS_DICTIONARY_DTL@CGXZCDB_ZSJ t WHERE t.DICT_SN = 'so_order_qualityUser' AND t.STR_KEY = t2.QUALITY_USER) AS "质保书单位",
             (SELECT STR_VALUE FROM SYS_DICTIONARY_DTL@CGXZCDB_ZSJ t WHERE t.DICT_SN = 'OUTBOUND_CALCULATE' AND t.STR_KEY = t2.OUTBOUND_CALCULATE) AS "出库计量",
             CASE WHEN OUTS_BILLET_FL = '1' THEN '外购' ELSE '自炼' END AS "外购坯",
             (SELECT STR_VALUE FROM SYS_DICTIONARY_DTL@CGXZCDB_ZSJ t WHERE t.DICT_SN = 'order_dtl_outs_type' AND t.STR_KEY = t2.OUTS_PIPE) AS "外购管",
             CASE WHEN MONITOR_FLAG = '1' THEN '监制' ELSE '不监制' END AS "是否监制",
             (SELECT STR_VALUE FROM SYS_DICTIONARY_DTL@CGXZCDB_ZSJ t WHERE t.DICT_SN = 'MAN_INSPECTION' AND t.STR_KEY = t2.MAN_INSPECTION) AS "在线机探盲区",
             t2.PROJECT_NO AS "订单工程编号",
             CASE WHEN COOPERATE_FLAG = '1' THEN '是' ELSE '否' END AS "是否协同",
             t2.EXTRA_REQUIRE_OLD AS "合同附加要求",
             t2.PRO_TEC_REQUIRE_OLD AS "产品技术要求",
             (SELECT STR_VALUE FROM SYS_DICTIONARY_DTL@CGXZCDB_ZSJ t WHERE t.DICT_SN = 'PROTECT_TYPE' AND t.STR_KEY = t2.KEEP_TYPE) AS "保价类型",
             t2.FREIGHT AS "运费",
             t2.OUT_PRICE AS "出厂价",
             t2.PAK_PRICE AS "吊装费",
             t2.BALNCE_PRICE AS "结算价"
FROM SO_ORDER@CGXZCDB_ZSJ t1
LEFT JOIN SO_ORDER_dtl@CGXZCDB_ZSJ t2 ON t1.order_no = t2.order_no
WHERE t1.order_status <> 'XX' AND t2.order_status <> 'XX' AND t1.order_type <> 'X'

and ROWNUM<10
`

/*
因为 SLM_ORDER_LINE.ORDER_SPECIAL_DESC_C为blob
const orderFields = [
    // 订单头字段
    "ORD_PK", "MANAG_NO", "ORDER_NO", "CUSTM_ORDER_NO", "REVW_RPT_NO", "ORDER_TYP", "ORDER_TYP_DESC", "PROTCL_NO", "CUSTOMER_NO", "CUSTOMER_NM", "MARKET_SYS", "MARKET_SYS_DESC", "ABRVITION", "SUPPLY_UNIT", "PRICE_FL", "CURRENCY", "MNY_ASK_TYP", "STLMT_UNIT_NO", "STLMT_UNIT_NM", "FST_DRAWEE_UNIT", "LEGAL_PSN", "AGENT", "ADDRESS", "ZIP_CODE", "BANK", "ACCOUNT", "TAX_ID", "PHONE", "FAX", "ORDER_WGT", "ORDER_MNY_TAX", "ORDER_MNY_NTAX", "MNY_ASK_DESC", "SIGN_PERSON", "SIGN_DATE", "SIGN_ADDRESS", "ORDER_YEAR", "ORDER_BDATE", "ORDER_EDATE", "ORDER_SCAN_NO", "CUSTOMER_MODEL_FL", "ORDER_MODEL_NO", "ORDER_MODEL_DESC", "SALE_ORG", "SALE_ORG_DESC", "SALE_AREA_NO", "SALE_AREA_DESC", "MEMO", "BE_ORDER_STS", "BE_OBJ_STS_OP", "ORDER_STS", "ORDER_STS_DESC", "OBJ_STS_OP", "APPROVAL_MAN", "APPROVAL_TIME", "EFFECT_MAN", "EFFECT_TIME", "CLOSE_MAN", "CLOSE_TIME", "VALIDFLAG", "CREATE_NAME", "CREATE_DEPT", "CREATE_TIME", "UPDATE_NAME", "UPDATE_TIME", "DELETE_NAME", "DELETE_TIME", "ORD_PK_OLD", "BE_ORDER_STS_DESC", "PRICE_NO", "ORDER_CHGE_NO", "ISLOCK", "PRINT_PRICE", "PRICE_PLAN", "CONTAIN_TAX", "ORDER_ARCHIVE", "PASS_TIME", "CONFIRM_TIME", "TRADE_TYPE", "ORDER_CLASSIFY", "BUSINESS_TYPE", "PRINT_TIME", "PRINT_COUNT", "LOCAL_LAW", "ORDER_FILE", "IPMAC", "AREA_FLAG", "USERORDER_FILE", "DOCUMENTARY_NAME", "DOCUMENTARY_DEPT", "TH_EXAMINE_STATUS", "TH_ORDER_FALG_ZC", "ORDER_REQUIRE_STATUS", "PORT_OF_DESTINATION", "PENALTY_DESC", "CREATE_NAME_OLD", "SALE_AREA_NO_OLD", "SALE_AREA_DESC_OLD", "TH_INTERFACE_FLAG",
    
    // 订单行字段
    "ORD_PK", "ORD_LN_PK", "ORDER_SEQ", "CUST_ALPHA_NO", "PSC", "PSC_DESC", "MSC", "MSC_DESC", "MSC_STYLE", "USE_CODE", "USE_DESC", "PRD_CLS", "PRODUCCODE", "PRODUCNAME", "STD_CODE", "STD_NAME", "STEELCODE", "STEELNAME", "STD_STYLE", "STD_STYLE_DESC", "ORDER_SPEC_CODE", "ORDER_SPEC_DESC", "SPEC_CODE", "SPEC_NAME", "DIMATER", "HEIGHT", "MODEL_CODE", "MODEL_DESC", "LEN_NO", "LEN_DESC", "LEN_UNIT", "LEN_MIN", "LEN_MAX", "SHRT_LEN_RATO", "SHORTEST", "LONG_LEN_RATO", "LONGEST", "SHRT_STOVE", "AVG_LEN_MIN", "DIFRNC_LEN_MAX", "ENDLEN_SPECSTD", "SIGL_LEN", "ORDER_UNIT", "ORDER_QTY", "ORD_ENSU_TOTL_LEN", "PRDCT_WGT", "DELVRY_RANGE_TPE", "DELVRY_RANGE_MIN", "DELVRY_RANGE_MAX", "FINAL_USER", "FINAL_USER_DESC", "OUTS_BILLET_FL", "OUTS_STL_PIPE_FL", "ASSEL_FL", "PRODUCER_FL", "PRDCR_NO", "PRDCR_DESC", "MPS_FL", "MPS_NO", "MPS_DESC", "DELVRY_BDATE", "DELVRY_EDATE", "DELVRY_DESC", "TRANS_DESC", "ADD_ASK_NO", "ORDER_ADD_DESC", "ORDER_SPECIAL_FL", "ORDER_SPECIAL_HEAD", "ORDER_SPECIAL_PATH", "ORDER_SPECIAL_DESC", "ZBS", "ORDER_PRICE", "TRANS_PRICE", "TEST_ID", "TEST_PRICE", "OUT_PRICE", "BALNCE_PRICE", "BE_ORDER_LN_STATUS", "BE_OBJ_STS_OP", "ORDER_LN_STATUS", "OBJ_STS_OP", "MEMO", "VALIDFLAG", "CREATE_NAME", "CREATE_TIME", "UPDATE_NAME", "UPDATE_TIME", "DELETE_NAME", "DELETE_TIME", "ORD_LN_PK_OLD", "ISLOCK", "CUST_ALPHA_NO2", "ORDER_CHGE_NO", "CHGE_ORD_LN_PK", "BALNCE_PRICE_STS", "BALNCE_PRICE_ORG", "BALNCE_PRICE_ORG_STS", "PAK_PRICE", "AW", "MPS_NO_INPUT", "MAN_INSPECTION", "INSPECTION_VALUE", "MPS_NO_FILE", "QUALITY_USER", "QUALITY_USER_DESC", "OUTPUT_STD_NO", "PROJECT_NO", "CRAFT_NO", "CRAFT_NO_V", "ORDER_COUNT", "MPS_ID", "LOGOUT_REVIEW_NO", "PIC_FD_CHECK", "BALNCE_PRICE_UNIT", "TON_PRICE", "KEEP_TYPE", "AVG_LEN_MAX", "WBS_CODE", "OUTBOUND_CALCULATE", "ORDER_TEAM", "MODE_OF_TRANSPORT", "AUTO_MEASURE", "KEY_PROJECT_CODE", "KEY_PROJECT_NAME", "KEY_PROJECT_STATUS",
    
    // 订单行交付字段
    "ORD_PK", "ORD_LN_PK", "ORD_LN_DLY_PK", "DELIVERY_NO", "DELVRY_BDATE", "DELVRY_EDATE", "DELVRY_QTY", "ORDER_UNIT", "WGT_ESTMT", "ENSU_TOTL_LEN", "RECEIV_NO", "RECEIV_NM", "DELVRY_ADDR", "AGENCY_FL", "RECEIV_ADDR", "TRANSIT_TYP", "TRANSIT_PLAN", "TRANSIT_MNY", "BE_DELIVERY_STATUS", "BE_OBJ_STS_OP", "DELIVERY_STATUS", "OBJ_STS_OP", "VALIDFLAG", "CREATE_NAME", "CREATE_TIME", "UPDATE_NAME", "UPDATE_TIME", "DELETE_NAME", "DELETE_TIME", "ISLOCK", "PICKUP_FLAG", "EXTRA_FLAG", "IS_ADD", "IS_LIKU", "NLK_RSN", "NLK_CHECK", "LK_ID", "TD_ID", "PRESENT_STATUS"
];
*/

const 老产销 = `
select * from
    SLM_ORDER_HEAD@TGCXDB_105 t
        INNER JOIN SLM_ORDER_LINE@TGCXDB_105 T1 ON T.ORD_PK = T1.ORD_PK
        INNER JOIN SLM_ORDER_LINE_DELIVERY@TGCXDB_105 T2 ON T.ORD_PK = T2.ORD_PK
        AND T1.ORD_LN_PK = T2.ORD_LN_PK
where ROWNUM<10
`
const 老产销实际对比列 = `
SELECT
    t.ORDER_NO AS 合同号,
    T1.ORDER_SEQ AS 合同行号,
    T2.DELIVERY_NO AS 交货行号,
    t.CUSTM_ORDER_NO AS 客户合同号,
    t.ORDER_TYP_DESC AS 合同类型描述,
    t.CUSTOMER_NM AS 客户名称,
    t.CUSTOMER_NO,
    ( SELECT BASENAME FROM COM_BASE_INFO@TGCXDB_105 WHERE BASECODE = t.CURRENCY ) AS 币种,
    ( SELECT BASENAME FROM COM_BASE_INFO@TGCXDB_105 WHERE BASECODE = t.PRICE_FL ) AS 计价方式,
    ( SELECT BASENAME FROM COM_BASE_INFO@TGCXDB_105 WHERE BASECODE = t.MNY_ASK_TYP ) AS 付款方式,
    ( SELECT BASENAME FROM COM_BASE_INFO@TGCXDB_105 WHERE BASECODE = t.SUPPLY_UNIT ) AS 制造商,
    t.STLMT_UNIT_NM AS 结算单位名称,
    t.STLMT_UNIT_NO,
    ( SELECT BASENAME FROM COM_BASE_INFO@TGCXDB_105 WHERE BASECODE = T.FST_DRAWEE_UNIT ) AS 首次受票人,
    t.MNY_ASK_DESC AS 付款要求描述,
    t.SALE_ORG_DESC AS 销售组织,
    t.SALE_AREA_DESC AS 销售区域,
    t.ORDER_STS_DESC AS 合同状态,
    t.EFFECT_MAN AS 生效人,
    t.CREATE_NAME AS 创建人,
    t.UPDATE_NAME AS 修改人,
    t.DOCUMENTARY_NAME AS 跟单人,
    DECODE( t.CONTAIN_TAX, '1', '是', '否' ) AS 是否含税 ,
    ( SELECT BASENAME FROM COM_BASE_INFO@TGCXDB_105 WHERE BASECODE = T.ORDER_CLASSIFY ) AS 合同分类 ,
    t.PRINT_PRICE AS 打印价格,
    t.PRICE_PLAN AS 优惠方案,
    DECODE( t.ORDER_ARCHIVE, '1', '是', '否' ) AS 合同归档,
    ( SELECT BASENAME FROM COM_BASE_INFO@TGCXDB_105 WHERE BASECODE = T1.PRD_CLS ) AS 品种分类,
    T1.PSC_DESC AS 产品规范描述,
    T1.MSC_DESC AS 冶金规范描述,
    T1.USE_DESC AS 用途码描述,
    T1.PRODUCNAME AS 品名描述,
    T1.STD_NAME AS 标准名称,
    T1.STEELNAME AS 钢级名称,
    T1.STD_STYLE_DESC AS 标准类别描述,
    T1.ORDER_SPEC_DESC AS 订货规格描述,
    T1.SPEC_NAME AS 产品规格描述,
    T1.DIMATER AS 外径,
    T1.HEIGHT AS 壁厚,
    T1.MODEL_DESC AS 扣型描述,
    T1.LEN_DESC AS 长度描述,
    T1.LEN_UNIT AS 长度单位,
    T1.LEN_MIN AS 下限,
    T1.LEN_MAX AS 上限,
    T1.SHRT_LEN_RATO AS 短尺率,
    T1.SHORTEST AS 短尺最短,
    T1.LONG_LEN_RATO AS 长尺率,
    T1.LONGEST AS 长尺最长,
    T1.SHRT_STOVE AS 短尺随炉,
    T1.AVG_LEN_MIN AS 平均长度大于,
    T1.AVG_LEN_MAX AS 平均长度小于,
    T1.ENDLEN_SPECSTD AS 成品公差,
    T1.SIGL_LEN AS 单倍尺长,
    T1.ORDER_UNIT AS 订货计量单位,
    T1.ORDER_QTY AS 订货量,
    T1.ORD_ENSU_TOTL_LEN AS 订货保总长,
    T1.PRDCT_WGT AS 生产吨数,
    T1.DELVRY_RANGE_TPE AS 交货量差方式,
    T1.DELVRY_RANGE_MIN AS 交货量差下限,
    T1.DELVRY_RANGE_MAX AS 交货量差上限,
    T1.FINAL_USER_DESC AS 最终用户,
    T1.FINAL_USER,
    ( SELECT BASENAME FROM COM_BASE_INFO@TGCXDB_105 WHERE BASECODE = T1.OUTS_BILLET_FL ) AS 外购坯,
    ( SELECT BASENAME FROM COM_BASE_INFO@TGCXDB_105 WHERE BASECODE = T1.OUTS_STL_PIPE_FL ) AS 外购管,
    DECODE( T1.ASSEL_FL, '1', '允许', '不允许' ) AS ASSEL,
    DECODE( T1.PRODUCER_FL, '1', '监制', '不监制' ) AS 监制要求,
    T1.PRDCR_DESC AS 监制说明,
    DECODE( T1.MPS_FL, '1', '有', '无' ) AS MPS要求,
    T1.MPS_NO AS MPS编号,
    T1.MPS_DESC AS MPS说明,
    T1.ORDER_ADD_DESC AS 订货附加要求,
    T1.ORDER_SPECIAL_HEAD AS 特殊要求技术规范,
    T1.ORDER_PRICE AS 订货价,
    T1.TRANS_PRICE AS 运费,
    T1.TEST_PRICE AS 测算价,
    T1.OUT_PRICE AS 出厂价,
    T1.BALNCE_PRICE AS 结算价,
    T1.PAK_PRICE AS 吊装费,
    T1.TON_PRICE AS 吨价,
    DECODE( T1.KEEP_TYPE, '1', '保总价', '0', '保单价' ) AS 保价类型,
    ( SELECT BASENAME FROM COM_BASE_INFO@TGCXDB_105 WHERE BASECODE = T1.ORDER_LN_STATUS ) AS 合同行状态,
    DECODE( T1.BALNCE_PRICE_STS, '1', '有确认', '录入' ) AS 结算价状态,
    DECODE( T1.MAN_INSPECTION, '1', '可手探', '不可手探' ) AS 在线机探盲区,
    T1.INSPECTION_VALUE AS 在线机探盲区切长,
    T1.QUALITY_USER_DESC AS 质保书单位 ,
    T1.WBS_CODE AS WBS号,
    DECODE( T1.OUTBOUND_CALCULATE, '1', '实单过磅', '2', '实单按尺码单', '0', '实单不过磅' ) 出库计量,
    DECODE( T1.ORDER_TEAM, '1', '是', '0', '否' ) 是否协同,
    DECODE( T1.MODE_OF_TRANSPORT, '1', '散货', '2', '集装箱' ) 运输方式,
    DECODE( T1.AUTO_MEASURE, '1', '是', '0', '否' ) 管端自动测量 ,
    T1.KEY_PROJECT_NAME AS 攻关项目 ,
    ( SELECT BASENAME FROM COM_BASE_INFO@TGCXDB_105 WHERE BASECODE = T1.OUTPUT_STD_NO ) 输出标准号,
    t2.DELVRY_EDATE AS 交货结束日期,
    t2.DELVRY_QTY AS 交货量,
    t2.WGT_ESTMT AS 重量估算,
    t2.ENSU_TOTL_LEN AS 保总长,
    t2.RECEIV_NM AS 收货单位名称,
    t2.DELVRY_ADDR AS 交货地点,
    DECODE( t2.AGENCY_FL, '1', '待办', '0', '自提' ) AS 代办标识,
    t2.RECEIV_ADDR AS 指定地址,
    ( SELECT BASENAME FROM COM_BASE_INFO@TGCXDB_105 WHERE BASECODE = t2.TRANSIT_TYP ) AS 出厂方式,
    t2.TRANSIT_PLAN AS 运输方案,
    DECODE( t2.PICKUP_FLAG, '1', '自提', '0', '否' ) AS 自提标志
FROM
    SLM_ORDER_HEAD@TGCXDB_105 t
        INNER JOIN SLM_ORDER_LINE@TGCXDB_105 T1 ON T.ORD_PK = T1.ORD_PK
        INNER JOIN SLM_ORDER_LINE_DELIVERY@TGCXDB_105 T2 ON T.ORD_PK = T2.ORD_PK
        AND T1.ORD_LN_PK = T2.ORD_LN_PK
WHERE
    t.VALIDFLAG = '1'  and t1.VALIDFLAG = '1' and t2.VALIDFLAG='1'
and ROWNUM<10
`


const orderFields = [
    // 订单头字段
    "ORD_PK", "MANAG_NO", "ORDER_NO", "CUSTM_ORDER_NO", "REVW_RPT_NO", "ORDER_TYP", "ORDER_TYP_DESC", "PROTCL_NO", "CUSTOMER_NO", "CUSTOMER_NM", "MARKET_SYS", "MARKET_SYS_DESC", "ABRVITION", "SUPPLY_UNIT", "PRICE_FL", "CURRENCY", "MNY_ASK_TYP", "STLMT_UNIT_NO", "STLMT_UNIT_NM", "FST_DRAWEE_UNIT", "LEGAL_PSN", "AGENT", "ADDRESS", "ZIP_CODE", "BANK", "ACCOUNT", "TAX_ID", "PHONE", "FAX", "ORDER_WGT", "ORDER_MNY_TAX", "ORDER_MNY_NTAX", "MNY_ASK_DESC", "SIGN_PERSON", "SIGN_DATE", "SIGN_ADDRESS", "ORDER_YEAR", "ORDER_BDATE", "ORDER_EDATE", "ORDER_SCAN_NO", "CUSTOMER_MODEL_FL", "ORDER_MODEL_NO", "ORDER_MODEL_DESC", "SALE_ORG", "SALE_ORG_DESC", "SALE_AREA_NO", "SALE_AREA_DESC", "MEMO", "BE_ORDER_STS", "BE_OBJ_STS_OP", "ORDER_STS", "ORDER_STS_DESC", "OBJ_STS_OP", "APPROVAL_MAN", "APPROVAL_TIME", "EFFECT_MAN", "EFFECT_TIME", "CLOSE_MAN", "CLOSE_TIME", "VALIDFLAG", "CREATE_NAME", "CREATE_DEPT", "CREATE_TIME", "UPDATE_NAME", "UPDATE_TIME", "DELETE_NAME", "DELETE_TIME", "ORD_PK_OLD", "BE_ORDER_STS_DESC", "PRICE_NO", "ORDER_CHGE_NO", "ISLOCK", "PRINT_PRICE", "PRICE_PLAN", "CONTAIN_TAX", "ORDER_ARCHIVE", "PASS_TIME", "CONFIRM_TIME", "TRADE_TYPE", "ORDER_CLASSIFY", "BUSINESS_TYPE", "PRINT_TIME", "PRINT_COUNT", "LOCAL_LAW", "ORDER_FILE", "IPMAC", "AREA_FLAG", "USERORDER_FILE", "DOCUMENTARY_NAME", "DOCUMENTARY_DEPT", "TH_EXAMINE_STATUS", "TH_ORDER_FALG_ZC", "ORDER_REQUIRE_STATUS", "PORT_OF_DESTINATION", "PENALTY_DESC", "CREATE_NAME_OLD", "SALE_AREA_NO_OLD", "SALE_AREA_DESC_OLD", "TH_INTERFACE_FLAG",
    
    // 订单行字段
    "ORD_PK", "ORD_LN_PK", "ORDER_SEQ", "CUST_ALPHA_NO", "PSC", "PSC_DESC", "MSC", "MSC_DESC", "MSC_STYLE", "USE_CODE", "USE_DESC", "PRD_CLS", "PRODUCCODE", "PRODUCNAME", "STD_CODE", "STD_NAME", "STEELCODE", "STEELNAME", "STD_STYLE", "STD_STYLE_DESC", "ORDER_SPEC_CODE", "ORDER_SPEC_DESC", "SPEC_CODE", "SPEC_NAME", "DIMATER", "HEIGHT", "MODEL_CODE", "MODEL_DESC", "LEN_NO", "LEN_DESC", "LEN_UNIT", "LEN_MIN", "LEN_MAX", "SHRT_LEN_RATO", "SHORTEST", "LONG_LEN_RATO", "LONGEST", "SHRT_STOVE", "AVG_LEN_MIN", "DIFRNC_LEN_MAX", "ENDLEN_SPECSTD", "SIGL_LEN", "ORDER_UNIT", "ORDER_QTY", "ORD_ENSU_TOTL_LEN", "PRDCT_WGT", "DELVRY_RANGE_TPE", "DELVRY_RANGE_MIN", "DELVRY_RANGE_MAX", "FINAL_USER", "FINAL_USER_DESC", "OUTS_BILLET_FL", "OUTS_STL_PIPE_FL", "ASSEL_FL", "PRODUCER_FL", "PRDCR_NO", "PRDCR_DESC", "MPS_FL", "MPS_NO", "MPS_DESC", "DELVRY_BDATE", "DELVRY_EDATE", "DELVRY_DESC", "TRANS_DESC", "ADD_ASK_NO", "ORDER_ADD_DESC", "ORDER_SPECIAL_FL", "ORDER_SPECIAL_HEAD", "ORDER_SPECIAL_PATH", "ORDER_SPECIAL_DESC", "ZBS", "ORDER_PRICE", "TRANS_PRICE", "TEST_ID", "TEST_PRICE", "OUT_PRICE", "BALNCE_PRICE", "BE_ORDER_LN_STATUS", "BE_OBJ_STS_OP", "ORDER_LN_STATUS", "OBJ_STS_OP", "MEMO", "VALIDFLAG", "CREATE_NAME", "CREATE_TIME", "UPDATE_NAME", "UPDATE_TIME", "DELETE_NAME", "DELETE_TIME", "ORD_LN_PK_OLD", "ISLOCK", "CUST_ALPHA_NO2", "ORDER_CHGE_NO", "CHGE_ORD_LN_PK", "BALNCE_PRICE_STS", "BALNCE_PRICE_ORG", "BALNCE_PRICE_ORG_STS", "PAK_PRICE", "AW", "MPS_NO_INPUT", "MAN_INSPECTION", "INSPECTION_VALUE", "MPS_NO_FILE", "QUALITY_USER", "QUALITY_USER_DESC", "OUTPUT_STD_NO", "PROJECT_NO", "CRAFT_NO", "CRAFT_NO_V", "ORDER_COUNT", "MPS_ID", "LOGOUT_REVIEW_NO", "PIC_FD_CHECK", "BALNCE_PRICE_UNIT", "TON_PRICE", "KEEP_TYPE", "AVG_LEN_MAX", "WBS_CODE", "OUTBOUND_CALCULATE", "ORDER_TEAM", "MODE_OF_TRANSPORT", "AUTO_MEASURE", "KEY_PROJECT_CODE", "KEY_PROJECT_NAME", "KEY_PROJECT_STATUS",
    
    // 订单行交付字段
    "ORD_PK", "ORD_LN_PK", "ORD_LN_DLY_PK", "DELIVERY_NO", "DELVRY_BDATE", "DELVRY_EDATE", "DELVRY_QTY", "ORDER_UNIT", "WGT_ESTMT", "ENSU_TOTL_LEN", "RECEIV_NO", "RECEIV_NM", "DELVRY_ADDR", "AGENCY_FL", "RECEIV_ADDR", "TRANSIT_TYP", "TRANSIT_PLAN", "TRANSIT_MNY", "BE_DELIVERY_STATUS", "BE_OBJ_STS_OP", "DELIVERY_STATUS", "OBJ_STS_OP", "VALIDFLAG", "CREATE_NAME", "CREATE_TIME", "UPDATE_NAME", "UPDATE_TIME", "DELETE_NAME", "DELETE_TIME", "ISLOCK", "PICKUP_FLAG", "EXTRA_FLAG", "IS_ADD", "IS_LIKU", "NLK_RSN", "NLK_CHECK", "LK_ID", "TD_ID", "PRESENT_STATUS"
];

const 新老产销实际对比 =`
select
    S1.ORDER_NO
     ,S1.ORDER_NO_OLD
     ,S1.order_item_num
     ,CASE WHEN NVL(S1.CONTRACT_NO_CUST,'N/A')=客户合同号 THEN '相同' else S1.CONTRACT_NO_CUST ||' ；老：'||客户合同号 end  as  客户合同号
     ,S1.ORDER_TYPE ||' ；老：'||合同类型描述  as  合同类型描述
     ,CASE WHEN to_char(S1.SALE_INS)=销售组织 THEN '相同' else to_char(S1.SALE_INS) ||' ；老：'||销售组织 end  as  销售组织
     ,CASE WHEN to_char(S1.SALE_ORG)=销售区域 THEN '相同' else to_char(S1.SALE_ORG) ||' ；老：'||销售区域 end  as  销售区域
     ,CASE WHEN to_char(S1.SALESMAN)=创建人 THEN '相同' else to_char(S1.SALESMAN) ||' ；老：'||创建人 end  as  创建人
     ,CASE WHEN to_char(S1.DOCUMENTARY_NAME)=跟单人 THEN '相同'
           WHEN NVL(S1.DOCUMENTARY_NAME, 'H')=NVL(跟单人, 'H') THEN '相同'
           else to_char(S1.DOCUMENTARY_NAME) ||' ；老：'||跟单人 end  as  跟单人
     ,S1.COMPANY_ID ||' ；老：'||首次受票人  as  首次受票人
     ,CASE WHEN to_char(S1.CUSTOMER_NM)=客户名称 THEN '相同'
           when md1.code = S1.CUSTOMER_NO then '相同'
           WHEN s2.CUSTOMER_NO = '8000000617' THEN '相同'
           WHEN s2.CUSTOMER_NO = '1440002305' THEN '相同'
           else to_char(S1.CUSTOMER_NM) ||' ；老：'||客户名称 end  as  客户名称
     ,CASE WHEN to_char(S1.STLMT_UNIT_NM)=结算单位名称 THEN '相同'
           when md2.code = S1.STLMT_UNIT_NO then '相同'
           WHEN S2.STLMT_UNIT_NO = '8000000617' THEN '相同'
           WHEN S2.STLMT_UNIT_NO = '1440002305' THEN '相同'
           else to_char(S1.STLMT_UNIT_NM) ||' ；老：'||结算单位名称 end  as  结算单位名称
     ,S1.SUPPLY_UNIT ||' ；老：'||制造商  as  制造商
     ,CASE WHEN NVL(S1.PRICE_FL,'N/A')=计价方式 THEN '相同' else to_char(S1.PRICE_FL) ||' ；老：'||计价方式 end  as  计价方式
     ,CASE WHEN to_char(S1.CURRENCY)=币种 THEN '相同' else to_char(S1.CURRENCY) ||' ；老：'||币种 end  as  币种
     ,CASE WHEN NVL(S1.MNY_ASK_TYP,'N/A')=付款方式 THEN '相同' else to_char(S1.MNY_ASK_TYP) ||' ；老：'||付款方式 end  as  付款方式
     ,CASE WHEN to_char(S1.PROD_NAME)=品名描述 THEN '相同' else to_char(S1.PROD_NAME) ||' ；老：'||品名描述 end  as  品名描述
     ,CASE WHEN to_char(S1.QLY_GRADE_NO)=NVL(标准类别描述,'无') THEN '相同' else to_char(S1.QLY_GRADE_NO) ||' ；老：'||标准类别描述 end  as  标准类别描述
     ,CASE WHEN to_char(S1.TRADE_NO)=钢级名称 THEN '相同' else to_char(S1.TRADE_NO) ||' ；老：'||钢级名称 end  as  钢级名称
     ,CASE WHEN to_char(S1.PROD_STD)=标准名称 THEN '相同' else to_char(S1.PROD_STD) ||' ；老：'||标准名称 end  as  标准名称
     ,CASE WHEN to_char(S1.SALE_PROD_CODE_NAME)=品名描述 THEN '相同' else to_char(S1.SALE_PROD_CODE_NAME) ||' ；老：'||品名描述 end  as  订货品名描述
     ,CASE WHEN to_char(S1.SALE_SG_STD_NM)=标准名称 THEN '相同' else to_char(S1.SALE_SG_STD_NM) ||' ；老：'||标准名称 end  as  订货标准名称
     ,CASE WHEN to_char(S1.SALE_SHOP_SIGN_NAME)=钢级名称 THEN '相同' else to_char(S1.SALE_SHOP_SIGN_NAME) ||' ；老：'||钢级名称 end  as  订货钢级名称
     ,CASE WHEN S1.OUT_DIA=外径 THEN '相同'
           when S1.PROD_TYPE_NO = 'CQ' and  NVL(S1.OUT_DIA,0) != 0  then '相同'
           else S1.OUT_DIA ||' ；老：'||外径 end  as  外径
     ,CASE WHEN S1.WAL_THK=壁厚 THEN '相同'
           when S1.PROD_TYPE_NO = 'CQ' and  NVL(S1.WAL_THK,0) != 0  then '相同'
           else S1.WAL_THK ||' ；老：'||壁厚 end  as  壁厚
     ,CASE WHEN to_char(S1.MODEL_NAME)=扣型描述 THEN '相同' else to_char(S1.MODEL_NAME) ||' ；老：'||扣型描述 end  as  扣型描述
     ,CASE WHEN to_char(S1.SPEC_CODE_NAME)=产品规格描述 THEN '相同' else to_char(S1.SPEC_CODE_NAME) ||' ；老：'||产品规格描述 end  as  产品规格描述
     ,CASE WHEN to_char(S1.ORDER_SPEC_CODE_NAME)=订货规格描述 THEN '相同' else to_char(S1.ORDER_SPEC_CODE_NAME) ||' ；老：'||订货规格描述 end  as  订货规格描述
     ,CASE WHEN S1.WGT=交货量 THEN '相同'
           WHEN S1.id IN ('1927801479682134018', '1927844455376556034', '1927839200570474497')  then '相同'
           else S1.WGT ||' ；老：'||交货量 end  as  交货量
     ,CASE WHEN to_char(S1.UNIT)=订货计量单位 THEN '相同' else to_char(S1.UNIT) ||' ；老：'||订货计量单位 end  as  订货计量单位
     ,CASE WHEN S1.LEN_MIN=下限 THEN '相同' else S1.LEN_MIN ||' ；老：'||下限 end  as  长度下限
     ,CASE WHEN S1.LEN_MAX=上限 THEN '相同' else S1.LEN_MAX ||' ；老：'||上限 end  as  长度上限
     ,CASE WHEN to_char(S1.LEN_UNIT)=长度单位 THEN '相同' else to_char(S1.LEN_UNIT) ||' ；老：'||长度单位 end  as  长度单位
     ,CASE WHEN NVL(S1.ORD_ENSU_TOTL_LEN,0)=NVL(保总长,0) THEN '相同' else S1.ORD_ENSU_TOTL_LEN ||' ；老：'||保总长 end  as  保总长
     ,CASE WHEN nvl(S1.SHRT_LEN_RATO,0)=nvl(短尺率,0) THEN '相同' else S1.SHRT_LEN_RATO ||' ；老：'||短尺率 end  as  短尺率
     ,CASE WHEN S1.SHORTEST=nvl(短尺最短,0) THEN '相同' else S1.SHORTEST ||' ；老：'||短尺最短 end  as  短尺最短
     ,CASE WHEN to_char(S1.SHRT_STOVE)=短尺随炉 THEN '相同' else to_char(S1.SHRT_STOVE) ||' ；老：'||短尺随炉 end  as  短尺随炉
     ,CASE WHEN S1.AVG_LEN_MIN=nvl(平均长度大于,0) THEN '相同' else S1.AVG_LEN_MIN ||' ；老：'||平均长度大于 end  as  平均长度大于
     ,CASE WHEN S1.AVG_LEN_MAX=nvl(平均长度小于,0) THEN '相同' else S1.AVG_LEN_MAX ||' ；老：'||平均长度小于 end  as  平均长度小于
     ,CASE WHEN S1.DELVRY_RANGE_MIN=交货量差下限*-1 THEN '相同'
           when NVL(S1.DELVRY_RANGE_MIN, 0) < 0 AND NVL(交货量差下限,0) = ABS(NVL(S1.DELVRY_RANGE_MIN, 0))  then '相同'
           else S1.DELVRY_RANGE_MIN ||' ；老：'||交货量差下限*-1 end  as  交货量差下限
     ,CASE WHEN S1.DELVRY_RANGE_MAX=交货量差上限 THEN '相同' else S1.DELVRY_RANGE_MAX ||' ；老：'||交货量差上限 end  as  交货量差上限
     ,CASE WHEN nvl(S1.SIGL_LEN,0)=nvl(单倍尺长,0) THEN '相同' else S1.SIGL_LEN ||' ；老：'||单倍尺长 end  as  单倍尺长
--         ,s1.DELVRY_EDATE ||' ；老：'||TO_CHAR(交货结束日期, 'YYYY-MM-DD')  as  交货结束日期
     ,CASE WHEN TO_CHAR(s1.DELVRY_EDATE) =TO_CHAR(交货结束日期, 'YYYY-MM-DD') THEN '相同' else TO_CHAR(s1.DELVRY_EDATE) ||' ；老：'||TO_CHAR(交货结束日期, 'YYYY-MM-DD') end  as  交货结束日期
     ,CASE WHEN to_char(S1.CONSIGNEE_COM)=收货单位名称 THEN '相同' else to_char(S1.CONSIGNEE_COM) ||' ；老：'||收货单位名称 end  as  收货单位名称
     ,CASE WHEN to_char(S1.TRANS_TYPE)=出厂方式 THEN '相同' else to_char(S1.TRANS_TYPE) ||' ；老：'||出厂方式 end  as  出厂方式
     ,CASE WHEN to_char(S1.TRANS_MODE)=代办标识 THEN '相同' else to_char(S1.TRANS_MODE) ||' ；老：'||代办标识 end  as  代办标识
     ,CASE WHEN to_char(S1.FINAL_USER_NM)=最终用户 THEN '相同'
           when md3.code = S1.FINAL_USER then '相同'
           WHEN S2.FINAL_USER = '8000000617' THEN '相同'
           else to_char(S1.FINAL_USER_NM) ||' ；老：'||最终用户 end  as  最终用户
     ,CASE WHEN to_char(S1.QUALITY_USER)=质保书单位 THEN '相同' else to_char(S1.QUALITY_USER) ||' ；老：'||质保书单位 end  as  质保书单位
     ,CASE WHEN to_char(S1.OUTBOUND_CALCULATE)=出库计量 THEN '相同' else to_char(S1.OUTBOUND_CALCULATE) ||' ；老：'||出库计量 end  as  出库计量
     ,CASE WHEN to_char(S1.OUTS_BILLET_FL)=外购坯 THEN '相同' else to_char(S1.OUTS_BILLET_FL) ||' ；老：'||外购坯 end  as  外购坯
     ,CASE WHEN to_char(nvl(S1.OUTS_PIPE,'/'))=外购管 THEN '相同' else to_char(S1.OUTS_PIPE) ||' ；老：'||外购管 end  as  外购管
     ,CASE WHEN to_char(S1.MONITOR_FLAG)=监制要求 THEN '相同' else to_char(S1.MONITOR_FLAG) ||' ；老：'||监制要求 end  as  监制要求
     ,CASE WHEN to_char(S1.MAN_INSPECTION)=在线机探盲区 THEN '相同' else to_char(S1.MAN_INSPECTION) ||' ；老：'||在线机探盲区 end  as  在线机探盲区
     ,在线机探盲区切长
--        case when to_char(NVL(d.INSPECTION_VALUE,0)) = NVL(b.INSPECTION_VALUE,'0')  then '相同' else to_char(d.INSPECTION_VALUE) ||',新产销：'|| to_char(b.INSPECTION_VALUE) end 		"在线机探盲区切长",
     ,CASE WHEN to_char(S1.COOPERATE_FLAG)=是否协同 THEN '相同' else to_char(S1.COOPERATE_FLAG) ||' ；老：'||是否协同 end  as  是否协同
     ,CASE WHEN to_char(S1.EXTRA_REQUIRE_OLD)=订货附加要求 THEN '相同' else '不同' end  as  订货附加要求
     ,CASE WHEN to_char(S1.PRO_TEC_REQUIRE_OLD)=特殊要求技术规范 THEN '相同' else '不同' end  as  特殊要求技术规范
     ,CASE WHEN to_char(S1.KEEP_TYPE)=保价类型 THEN '相同' else to_char(S1.KEEP_TYPE) ||' ；老：'||保价类型 end  as  保价类型
     ,CASE WHEN S1.FREIGHT=nvl(运费,0) THEN '相同' else S1.FREIGHT ||' ；老：'||运费 end  as  运费
     ,CASE WHEN S1.OUT_PRICE=nvl(出厂价,0) THEN '相同' else S1.OUT_PRICE ||' ；老：'||出厂价 end  as  出厂价
     ,CASE WHEN S1.PAK_PRICE=nvl(吊装费,0) THEN '相同' else S1.PAK_PRICE ||' ；老：'||吊装费 end  as  吊装费
     ,CASE WHEN S1.BALNCE_PRICE=nvl(结算价,0) THEN '相同' else S1.BALNCE_PRICE ||' ；老：'||结算价 end  as  结算价
from (
         SELECT
             t2.id,
             t1.order_no,--合同号
             t1.ORDER_NO_OLD,--老产销订单
             t2.ORDER_NO_OLD || '/' ||t2.ORDER_SEQ_OLD || '/' || t2.DELIVERY_NO_OLD AS order_item_num ,
             t1.CONTRACT_NO_CUST,--客户合同号
             t1.TRADE_TYPE,--贸易类型
             t1.CUSTOMER_NO,
             t1.STLMT_UNIT_NO,
             t2.PROD_TYPE_NO,
             case when (select  STR_VALUE from SYS_DICTIONARY_DTL@CGXZCDB_ZSJ  t where t.DICT_SN='ORDER_CLASSIFY' and t.STR_KEY=t1.ORDER_CLASSIFY) is not null then (select  STR_VALUE from SYS_DICTIONARY_DTL@CGXZCDB_ZSJ  t where t.DICT_SN='ORDER_CLASSIFY' and t.STR_KEY=t1.ORDER_CLASSIFY) else  t1.ORDER_CLASSIFY end ORDER_CLASSIFY,--分类
             (select  STR_VALUE from SYS_DICTIONARY_DTL@CGXZCDB_ZSJ  t where t.DICT_SN='ORDER_MODE' and t.STR_KEY=t1.ORDER_MODE  ) ORDER_MODE,--合同类型
             (select  STR_VALUE from SYS_DICTIONARY_DTL@CGXZCDB_ZSJ  t where t.DICT_SN='ORDER_TYPE' and t.STR_KEY=t1.ORDER_TYPE ) ORDER_TYPE,--分类--订货方式
             (select  STR_VALUE from SYS_DICTIONARY_DTL@CGXZCDB_ZSJ  t where t.DICT_SN='saleIns' and t.STR_KEY=t1.SALE_INS ) SALE_INS,--销售组织
             (select  STR_NAME from hr_department@CGXZCDB_ZSJ t where STR_SN=SALE_ORG) SALE_ORG,--片区
-- 		ORDER_YEAR,--合同年度
             case when  (select  NAME from HR_USER@CGXZCDB_ZSJ where USER_NO=t1.SALESMAN) is not null then (select  NAME from HR_USER@CGXZCDB_ZSJ where USER_NO=t1.SALESMAN) else  t1.SALESMAN end SALESMAN,--业务员
             case when  (select  NAME from HR_USER@CGXZCDB_ZSJ where USER_NO=t1.DOCUMENTARY_NAME) is not null then (select  NAME from HR_USER@CGXZCDB_ZSJ where USER_NO=t1.DOCUMENTARY_NAME) else  t1.DOCUMENTARY_NAME end DOCUMENTARY_NAME,--跟单人
             t1.SIGN_DATE,--签订日期
             case when t1.BUSINESS_TYPE='1' then '出口' else '非出口' end BUSINESS_TYPE,--业务分类
             t1.SIGN_ADDRESS,--签订地点
             (select  STR_VALUE from SYS_DICTIONARY_DTL@CGXZCDB_ZSJ  t where t.DICT_SN='saleOrg' and t.STR_KEY=t1.COMPANY_ID) COMPANY_ID,--卖出单位
             t1.CUSTOMER_NM,--订货单位
             t1.STLMT_UNIT_NM,--结算单位
             case when t1.PRODUCE_PLACE='Z' then '自产' else '外购' end PRODUCE_PLACE,--产地区分
             (select  STR_VALUE from SYS_DICTIONARY_DTL@CGXZCDB_ZSJ  t where t.DICT_SN='SUPPLY_UNIT' and t.STR_KEY=t1.SUPPLY_UNIT ) SUPPLY_UNIT,--制造商
             (select  STR_VALUE from SYS_DICTIONARY_DTL@CGXZCDB_ZSJ  t where t.DICT_SN='SETTLE_FREIGHT_MODE' and t.STR_KEY=t1.FREIGHT_MODE )  FREIGHT_MODE,--运费开票方式
             (select  STR_VALUE from SYS_DICTIONARY_DTL@CGXZCDB_ZSJ  t where t.DICT_SN='ORDER_INVOICE_TYPE' and t.STR_KEY=t1.INVOICE_TYPE ) INVOICE_TYPE,--发票类型
             (select  STR_VALUE from SYS_DICTIONARY_DTL@CGXZCDB_ZSJ  t where t.DICT_SN='saleBillDate' and t.STR_KEY=t1.BILL_DATE ) BILL_DATE,
             t1.BANK,--开户行
             t1.ACCOUNT,--开户行账号
             t1.TAX_ID,--税号
             (select  STR_VALUE from SYS_DICTIONARY_DTL@CGXZCDB_ZSJ  t where t.DICT_SN='PRICE_FL' and t.STR_KEY=t1.PRICE_FL) PRICE_FL,--定价方式
-- 		CONTAIN_TAX,--是否含税
             (select  STR_VALUE from SYS_DICTIONARY_DTL@CGXZCDB_ZSJ  t where t.DICT_SN='currency' and t.STR_KEY=t1.CURRENCY)  CURRENCY,--币种
             (select  STR_VALUE from SYS_DICTIONARY_DTL@CGXZCDB_ZSJ  t where t.DICT_SN='MNY_ASK_TYP' and t.STR_KEY=t1.MNY_ASK_TYP)  MNY_ASK_TYP,--付款方式
             t1.PRICE_PLAN,--优惠方案
-- 		MNY_ASK_DESC,--付款要求
             t1.LOCAL_LAW,--当地法律法规
             '',--其他信息
             t1.ORDER_REQUIRE_STATUS,--订单要求
             t1.PORT_OF_DESTINATION,--目的港
-- 		'',--罚则
-- 		PENALTY_DESC--罚则条款
             t2.ORDER_SEQ,--合同行
             t2.ORDER_SEQ_OLD,--老产销合同行
             t2.DELIVERY_NO_OLD,--老产销交货行
             (select  STR_VALUE from SYS_DICTIONARY_DTL@CGXZCDB_ZSJ  t where t.DICT_SN='orderDtlStatus' and t.STR_KEY=t2.ORDER_STATUS )  ORDER_STATUS,--状态
             t2.MONTH,--排产月份
             t2.GROUP_ORDER_NO||'/'||GROUP_ORDER_SEQ GROUP_ORDER_NO, --集团订单号
             t2.PROD_SPEC_CODE,--规范码
             t2.PROD_SPEC,--产品规范描述
             t2.APN_CODE,--用途码代码
             t2.APN_DEF,--用途码描述
             t2.PROD_NAME,--规范品名
             (select  STR_VALUE from SYS_DICTIONARY_DTL@CGXZCDB_ZSJ  t where t.DICT_SN='qualityLevels' and t.STR_VALUE2=t2.QLY_GRADE_NO  ) QLY_GRADE_NO,--质量等级
             t2.TRADE_NO,--规范钢级
             t2.PROD_STD,--规范标准
             (select  STR_VALUE from SYS_DICTIONARY_DTL@CGXZCDB_ZSJ  t where t.DICT_SN='pipeType' and t.STR_KEY=t2.PIPE_TYPE  )  PIPE_TYPE,--管端类型
             (select  STR_VALUE from SYS_DICTIONARY_DTL@CGXZCDB_ZSJ  t where t.DICT_SN='manuMethod' and t.STR_KEY=t2.MANU_METHOD  ) MANU_METHOD,--制造方式
             t2.DELIVERY_STATUS_NAME,--规范交货状态
             t2.SALE_PROD_CODE_NAME,--订货品名
             t2.SALE_SG_STD_NM,--订货标准
             t2.SALE_SHOP_SIGN_NAME,--订货钢级
             t2.SALE_GOOD_STATUS_NAME,--订货交货状态
             t2.OUT_DIA,--外径
             t2.WAL_THK,--壁厚
             t2.MODEL_NAME,--扣型
             t2.SPEC_CODE_NAME,--产品规格
             t2.ORDER_SPEC_CODE_NAME,--订货规格
             t2.WGT,--订货量
             (select  STR_VALUE from SYS_DICTIONARY_DTL@CGXZCDB_ZSJ  t where t.DICT_SN='orderUnit' and t.STR_KEY=t2.UNIT  ) UNIT,--订货单位
             t2.LEN_MIN,--长度下限
             t2.LEN_MAX,--长度上限
             (select  STR_VALUE from SYS_DICTIONARY_DTL@CGXZCDB_ZSJ  t where t.DICT_SN='LEN_UNIT' and t.STR_KEY=t2.LEN_UNIT  ) LEN_UNIT,--长度单位
             t2.ORD_ENSU_TOTL_LEN,--保总长
             t2.SHRT_LEN_RATO,--短尺率
             t2.SHORTEST,--短尺最短
             t2.SHRT_STOVE,--短尺随炉
             t2.AVG_LEN_MIN,--平均长度大于
             t2.AVG_LEN_MAX,--平均长度小于
             t2.DELVRY_RANGE_MIN,--交货量差下限
             t2.DELVRY_RANGE_MAX,--交货量差上限
             t2.SIGL_LEN,--单倍长度
             t2.DELVRY_EDATE,--交货期
             t2.CONSIGNEE_COM,--收货单位
             (select  STR_VALUE from SYS_DICTIONARY_DTL@CGXZCDB_ZSJ  t where t.DICT_SN='TRANS_TYPE' and t.STR_KEY=t2.TRANS_TYPE  ) TRANS_TYPE,--运输方式
             (select  STR_VALUE from SYS_DICTIONARY_DTL@CGXZCDB_ZSJ  t where t.DICT_SN='transMode' and t.STR_KEY=t2.TRANS_MODE  ) TRANS_MODE,--运输模式
             t2.STATION_NAME,--到站
             t2.SPCL_LN_NM,--专用线
             t2.MEMO,--备注
             t2.FINAL_USER_NM,--最终用户
             t2.FINAL_USER,
             (select  STR_VALUE from SYS_DICTIONARY_DTL@CGXZCDB_ZSJ  t where t.DICT_SN='so_order_qualityUser' and t.STR_KEY=t2.QUALITY_USER  ) QUALITY_USER,--质保书单位
             (select  STR_VALUE from SYS_DICTIONARY_DTL@CGXZCDB_ZSJ  t where t.DICT_SN='OUTBOUND_CALCULATE' and t.STR_KEY=t2.OUTBOUND_CALCULATE  ) OUTBOUND_CALCULATE,--出库计量
             case when OUTS_BILLET_FL='1' then '外购' else '自炼' end OUTS_BILLET_FL,--外购坯
             (select  STR_VALUE from SYS_DICTIONARY_DTL@CGXZCDB_ZSJ  t where t.DICT_SN='order_dtl_outs_type' and t.STR_KEY=t2.OUTS_PIPE  ) OUTS_PIPE,--外购管
             case when MONITOR_FLAG='1' then '监制' else '不监制' end MONITOR_FLAG,--是否监制
             (select  STR_VALUE from SYS_DICTIONARY_DTL@CGXZCDB_ZSJ  t where t.DICT_SN='MAN_INSPECTION' and t.STR_KEY=t2.MAN_INSPECTION  ) MAN_INSPECTION,--在线机探盲区
             t2.PROJECT_NO,--订单工程编号
             case when COOPERATE_FLAG='1' then '是' else '否' end COOPERATE_FLAG,--是否协同
             t2.EXTRA_REQUIRE_OLD,--合同附加要求
             t2.PRO_TEC_REQUIRE_OLD,--产品技术要求
             (select  STR_VALUE from SYS_DICTIONARY_DTL@CGXZCDB_ZSJ  t where t.DICT_SN='PROTECT_TYPE' and t.STR_KEY=t2.KEEP_TYPE  ) KEEP_TYPE,--保价类型
             t2.FREIGHT,--运费
             t2.OUT_PRICE,--出厂价
             t2.PAK_PRICE,--吊装费
             t2.BALNCE_PRICE--结算价
         from SO_ORDER@CGXZCDB_ZSJ t1  left join  SO_ORDER_dtl@CGXZCDB_ZSJ   t2 on t1.order_no=t2.order_no  where  t1.order_status<>'XX' and t2.order_status<>'XX'  and t1.order_type<>'X'
--                                                                                                          and  t1.ORDER_NO_old = 'XSXPQ-25A1468'
     ) s1 left JOIN
     (SELECT
          t.ORDER_NO AS 合同号,
          T1.ORDER_SEQ AS 合同行号,
          T2.DELIVERY_NO AS 交货行号,
          t.CUSTM_ORDER_NO AS 客户合同号,
          t.ORDER_TYP_DESC AS 合同类型描述,
          t.CUSTOMER_NM AS 客户名称,
          t.CUSTOMER_NO,
          ( SELECT BASENAME FROM COM_BASE_INFO@TGCXDB_105 WHERE BASECODE = t.CURRENCY ) AS 币种,
          ( SELECT BASENAME FROM COM_BASE_INFO@TGCXDB_105 WHERE BASECODE = t.PRICE_FL ) AS 计价方式,
          ( SELECT BASENAME FROM COM_BASE_INFO@TGCXDB_105 WHERE BASECODE = t.MNY_ASK_TYP ) AS 付款方式,
          ( SELECT BASENAME FROM COM_BASE_INFO@TGCXDB_105 WHERE BASECODE = t.SUPPLY_UNIT ) AS 制造商,
          t.STLMT_UNIT_NM AS 结算单位名称,
          t.STLMT_UNIT_NO,
          ( SELECT BASENAME FROM COM_BASE_INFO@TGCXDB_105 WHERE BASECODE = T.FST_DRAWEE_UNIT ) AS 首次受票人,
          t.MNY_ASK_DESC AS 付款要求描述,
          t.SALE_ORG_DESC AS 销售组织,
          t.SALE_AREA_DESC AS 销售区域,
          t.ORDER_STS_DESC AS 合同状态,
          t.EFFECT_MAN AS 生效人,
          t.CREATE_NAME AS 创建人,
          t.UPDATE_NAME AS 修改人,
          t.DOCUMENTARY_NAME AS 跟单人,
          DECODE( t.CONTAIN_TAX, '1', '是', '否' ) AS 是否含税 ,
          ( SELECT BASENAME FROM COM_BASE_INFO@TGCXDB_105 WHERE BASECODE = T.ORDER_CLASSIFY ) AS 合同分类 ,
          t.PRINT_PRICE AS 打印价格,
          t.PRICE_PLAN AS 优惠方案,
          DECODE( t.ORDER_ARCHIVE, '1', '是', '否' ) AS 合同归档,
          ( SELECT BASENAME FROM COM_BASE_INFO@TGCXDB_105 WHERE BASECODE = T1.PRD_CLS ) AS 品种分类,
          T1.PSC_DESC AS 产品规范描述,
          T1.MSC_DESC AS 冶金规范描述,
          T1.USE_DESC AS 用途码描述,
          T1.PRODUCNAME AS 品名描述,
          T1.STD_NAME AS 标准名称,
          T1.STEELNAME AS 钢级名称,
          T1.STD_STYLE_DESC AS 标准类别描述,
          T1.ORDER_SPEC_DESC AS 订货规格描述,
          T1.SPEC_NAME AS 产品规格描述,
          T1.DIMATER AS 外径,
          T1.HEIGHT AS 壁厚,
          T1.MODEL_DESC AS 扣型描述,
          T1.LEN_DESC AS 长度描述,
          T1.LEN_UNIT AS 长度单位,
          T1.LEN_MIN AS 下限,
          T1.LEN_MAX AS 上限,
          T1.SHRT_LEN_RATO AS 短尺率,
          T1.SHORTEST AS 短尺最短,
          T1.LONG_LEN_RATO AS 长尺率,
          T1.LONGEST AS 长尺最长,
          T1.SHRT_STOVE AS 短尺随炉,
          T1.AVG_LEN_MIN AS 平均长度大于,
          T1.AVG_LEN_MAX AS 平均长度小于,
          T1.ENDLEN_SPECSTD AS 成品公差,
          T1.SIGL_LEN AS 单倍尺长,
          T1.ORDER_UNIT AS 订货计量单位,
          T1.ORDER_QTY AS 订货量,
          T1.ORD_ENSU_TOTL_LEN AS 订货保总长,
          T1.PRDCT_WGT AS 生产吨数,
          T1.DELVRY_RANGE_TPE AS 交货量差方式,
          T1.DELVRY_RANGE_MIN AS 交货量差下限,
          T1.DELVRY_RANGE_MAX AS 交货量差上限,
          T1.FINAL_USER_DESC AS 最终用户,
          T1.FINAL_USER,
          ( SELECT BASENAME FROM COM_BASE_INFO@TGCXDB_105 WHERE BASECODE = T1.OUTS_BILLET_FL ) AS 外购坯,
          ( SELECT BASENAME FROM COM_BASE_INFO@TGCXDB_105 WHERE BASECODE = T1.OUTS_STL_PIPE_FL ) AS 外购管,
          DECODE( T1.ASSEL_FL, '1', '允许', '不允许' ) AS ASSEL,
          DECODE( T1.PRODUCER_FL, '1', '监制', '不监制' ) AS 监制要求,
          T1.PRDCR_DESC AS 监制说明,
          DECODE( T1.MPS_FL, '1', '有', '无' ) AS MPS要求,
          T1.MPS_NO AS MPS编号,
          T1.MPS_DESC AS MPS说明,
          T1.ORDER_ADD_DESC AS 订货附加要求,
          T1.ORDER_SPECIAL_HEAD AS 特殊要求技术规范,
          T1.ORDER_PRICE AS 订货价,
          T1.TRANS_PRICE AS 运费,
          T1.TEST_PRICE AS 测算价,
          T1.OUT_PRICE AS 出厂价,
          T1.BALNCE_PRICE AS 结算价,
          T1.PAK_PRICE AS 吊装费,
          T1.TON_PRICE AS 吨价,
          DECODE( T1.KEEP_TYPE, '1', '保总价', '0', '保单价' ) AS 保价类型,
          ( SELECT BASENAME FROM COM_BASE_INFO@TGCXDB_105 WHERE BASECODE = T1.ORDER_LN_STATUS ) AS 合同行状态,
          DECODE( T1.BALNCE_PRICE_STS, '1', '有确认', '录入' ) AS 结算价状态,
          DECODE( T1.MAN_INSPECTION, '1', '可手探', '不可手探' ) AS 在线机探盲区,
          T1.INSPECTION_VALUE AS 在线机探盲区切长,
          T1.QUALITY_USER_DESC AS 质保书单位 ,
          T1.WBS_CODE AS WBS号,
          DECODE( T1.OUTBOUND_CALCULATE, '1', '实单过磅', '2', '实单按尺码单', '0', '实单不过磅' ) 出库计量,
          DECODE( T1.ORDER_TEAM, '1', '是', '0', '否' ) 是否协同,
          DECODE( T1.MODE_OF_TRANSPORT, '1', '散货', '2', '集装箱' ) 运输方式,
          DECODE( T1.AUTO_MEASURE, '1', '是', '0', '否' ) 管端自动测量 ,
          T1.KEY_PROJECT_NAME AS 攻关项目 ,
          ( SELECT BASENAME FROM COM_BASE_INFO@TGCXDB_105 WHERE BASECODE = T1.OUTPUT_STD_NO ) 输出标准号,
          t2.DELVRY_EDATE AS 交货结束日期,
          t2.DELVRY_QTY AS 交货量,
          t2.WGT_ESTMT AS 重量估算,
          t2.ENSU_TOTL_LEN AS 保总长,
          t2.RECEIV_NM AS 收货单位名称,
          t2.DELVRY_ADDR AS 交货地点,
          DECODE( t2.AGENCY_FL, '1', '待办', '0', '自提' ) AS 代办标识,
          t2.RECEIV_ADDR AS 指定地址,
          ( SELECT BASENAME FROM COM_BASE_INFO@TGCXDB_105 WHERE BASECODE = t2.TRANSIT_TYP ) AS 出厂方式,
          t2.TRANSIT_PLAN AS 运输方案,
          DECODE( t2.PICKUP_FLAG, '1', '自提', '0', '否' ) AS 自提标志
      FROM
          SLM_ORDER_HEAD@TGCXDB_105 t
              INNER JOIN SLM_ORDER_LINE@TGCXDB_105 T1 ON T.ORD_PK = T1.ORD_PK
              INNER JOIN SLM_ORDER_LINE_DELIVERY@TGCXDB_105 T2 ON T.ORD_PK = T2.ORD_PK
              AND T1.ORD_LN_PK = T2.ORD_LN_PK
      WHERE
          t.VALIDFLAG = '1'  and t1.VALIDFLAG = '1' and t2.VALIDFLAG='1'
--           and T.ORDER_NO = 'XSXPQ-25A1468'
     ) s2 on s1.order_no_old = s2.合同号 and s1.ORDER_SEQ_OLD = s2.合同行号 and s1.DELIVERY_NO_OLD=s2.交货行号

          LEFT JOIN (
    SELECT CUSTOMER_NO, code,
           ROW_NUMBER() OVER (PARTITION BY CUSTOMER_NO ORDER BY id DESC) AS rn
    FROM MD_SUPPLY_OLD_SYSTEM_LINK@TGCXDB_105
)md1 ON md1.CUSTOMER_NO = s2.CUSTOMER_NO AND md1.rn = 1
          LEFT JOIN (
    SELECT CUSTOMER_NO, code,
           ROW_NUMBER() OVER (PARTITION BY CUSTOMER_NO ORDER BY id DESC) AS rn
    FROM MD_SUPPLY_OLD_SYSTEM_LINK@TGCXDB_105
)md2 ON md2.CUSTOMER_NO = s2.STLMT_UNIT_NO AND md2.rn = 1
          LEFT JOIN (
    SELECT CUSTOMER_NO, code,
           ROW_NUMBER() OVER (PARTITION BY CUSTOMER_NO ORDER BY id DESC) AS rn
    FROM MD_SUPPLY_OLD_SYSTEM_LINK@TGCXDB_105
)md3 ON md3.CUSTOMER_NO = s2.FINAL_USER AND md3.rn = 1

where ROWNUM<10
`


  const date = moment().format('YYYY-MM-DD HH-mm');
  const dateSubFix = `99999-${date}`;
// 使用连接池
async function run() {
  let pool;
  try {
    pool = await oracledb.createPool(DB_CONFIG);
    let connection;
    try {
      connection = await pool.getConnection();
    //   connection.execute('select * from AA_AARF ',(err,result)=>{
    //     console.log('result:',result)
    //   })
     // const 新产销与老产销Result = await 


      connection.execute(新产销,(err,result)=>{
          console.log('新产销:',result.metaData.length)
      })

      connection.execute(新产销对比列,(err,result)=>{
          console.log('新产销对比列:',result.metaData.map(s=>s.name))
      })


      connection.execute(老产销实际对比列,(err,result)=>{
          console.log('老产销实际对比列:',result.metaData.map(s=>s.name))
      })


      


      connection.execute(新老产销实际对比,(err,result)=>{
          console.log('新老产销实际对比:',result.metaData.map(s=>s.name))
      })



      //  connection.execute(老产销,(err,result)=>{
      //     console.log('老产销:',result.metaData.length)
      // })

       console.log('老产销:',orderFields.length)

      


     
    //  connection.execute(新产销与老产销,(err,result)=>{
    //   console.log('新产销与老产销:',result.metaData.length)

    // //    connection.execute("select * from AA_AARF",(err,result)=>{
    //   //  console.log('err,result:',err,result)
    //         // const name = `新产销与老产销-67${dateSubFix}`
    //         // var buffer = xlsx.build([
    //         //     {name:'统计',data:[result.metaData.map(s=>s.name),...result.rows]},
    //         // ]);
    //         //  fs.writeFile(`./${name}.xlsx`,buffer,{flush:true},(err)=>{

    //         //  });
    //   });
    //   connection.execute(新产销与老产销+ ` where S1.PROD_SPEC_CODE != '99999'`,(err,result)=>{
    //         const name = `新产销与老产销-非${dateSubFix}`
    //         var buffer = xlsx.build([
    //             {name:'统计',data:[result.metaData.map(s=>s.name),...result.rows]},
    //         ]);
    //          fs.writeFileSync(`./${name}.xlsx`,buffer,{'flag':'w'});
    //   });
    //   connection.execute(新产销与老产销+ ` where S1.PROD_SPEC_CODE = '99999'`,(err,result)=>{
    //          const name = `新产销与老产销-${dateSubFix}`
    //         var buffer = xlsx.build([
    //              {name:'统计',data:[result.metaData.map(s=>s.name),...result.rows]},
    //         ]);
    //          fs.writeFileSync(`./${name}.xlsx`,buffer,{'flag':'w'});
    //   });

    } catch (err) {
      throw (err);
    } finally {
      if (connection) {
        try {
          await connection.close(); 
        } catch (err) {
          throw (err);
        }
      }
    }
  } catch (err) {
    console.log(err)
    throw err;
  } finally {
    await pool.close();
  }
}
run()